HAVING and CALCULATED in PROC SQL

HAVING: The distinction between HAVING and the WHERE clause is that HAVING conditions can reference summary statistics and are evaluated after aggregations are performed. Thus they take effect “downstream,” on the output side of the process. So we can use WHERE statement in the data sets to replace this HAVING, as is done below.

CALCULATED: if you want to use the calculated result during the SQL select clause, you need to add CALCULATED otherwise there will be error like: ERROR: The following columns were not found in the contributing tables: price_range

In the following the first one is faster than the second, although they generate the same results.

proc sql;
       create table outdata.event_trends1 as
       select event_id, active_max_price-active_min_price as price_range, max(calculated price_range) as max_range, datepart(trend_time) as trend_date
       from fansnapd.event_trends
       group by event_id
       having calculated price_range=max_range
       order by event_id;
quit;

proc sql;
       create table outdata.event_trends(where=(price_range=max_range)) as
       select event_id, active_max_price-active_min_price as price_range, max(calculated price_range) as max_range, datepart(trend_time) as trend_date
       from fansnapd.event_trends
       group by event_id
       order by event_id;
quit;

Variables order in the KEEP RENAME WHERE INDEX clause in the data sets

Variables order in the KEEP RENAME WHERE INDEX clause in the data sets:

1) KEEP = variables name exactly the same as original data set

2) RENAME change the original variable name to the new one

3) WHERE INDEX should use the new name

In the following clicks table has variable event_id guid is_cpa.

proc sort data= clicks (keep=event_id guid is_cpa rename=(event_id=id is_cpa=cpa guid=fsid)) where=(cpa=1) out=test1(index=(fsid));

by id;

run;

SAS study notes: index in sas: when to use index, how to create index, how to modify index / datasets / variables without losing index

Index

When to use index:

1: yield faster access to small subsets of obs for WHERE processing

2: return obs in sorted order for BY processing

3: perform table lookup operations

4: join obs

5: modify obs

Simple index: (index=(myindex)) or (index=(lastname firstname))

Composite index: (index=(comp_index=(lastname firstname)))

Pcoc datasets library=libref;

Modify sas_data_set;

Index delete index_name;

Index create index_specification;

Quit;

Proc sql;

Create index index_name on table_name;

Drop index index_name from table_name;

Quit;

Task	Effect
Add observation(s) to data set	Value/identifier pairs are added to index(es).
Delete observation(s) from data set	Value/identifier pairs are deleted from index(es).
Update observation(s) in data set	Value/identifier pairs are updated in index(es).
Delete data set	The index file is deleted.
Rebuild data set with DATA step	The index file is deleted.
Sort the data in place with the FORCE option in PROC SORT	The index file is deleted.

Use PROC DATASETS to copy, rename(change) datasets, rename variables

DATA SAS-data-file-name (INDEX=

(index-specification-1</UNIQUE><...index-specification-n</UNIQUE>>));

SET SAS-data-set-name ;

RUN;

PROC DATASETS LIBRARY=libref ;

MODIFY SAS-data-set-name;

INDEX DELETE index-name ;

INDEX CREATE index-specification;

QUIT;

PROC SQL;

CREATE INDEX index-name

ON table-name(column-name-1<...,column-name-n>);

DROP INDEX index-name FROM table-name ;

QUIT;

PROC CONTENTS DATA=SAS-data-set-name;

RUN;

PROC DATASETS <LIBRARY=libref> ;

CONTENTS DATA=SAS-data-set-name;

QUIT;

PROC DATASETS LIBRARY=old-libref ;

COPY OUT=new-libref;

SELECT SAS-data-set-name;

QUIT;

PROC COPY OUT=new-libref IN=old-libref ;

SELECT SAS-data-set-name(s);

RUN;

QUIT;

PROC DATASETS LIBRARY=libref ;

CHANGE old-data-set-name = new-data-set-name;

QUIT;

PROC DATASETS LIBRARY=libref ;

MODIFY SAS-data-set-name;

RENAME old-var-name-1 = new-var-name-1 ;

<...old-var-name-n = new-var-name-n> ;

QUIT;

R vs SAS 1: R aggregate v.s. SAS proc summary

In SAS, it's convenient to calculate mean/sum alike statistics over different subset of the original data using proc summary.

In R we can get the similar result using function "aggregate", or use "tapply" for simple condition.

Example:

library(stats)
aggregate(cbind(ncases, ncontrols) ~ alcgp + tobgp, data = esoph, sum)-> data1
aggregate(cbind(ncases, ncontrols) ~ alcgp , data = esoph, sum)-> data2
merge(data1, data2, by.x="alcgp", by.y="alcgp")

gives us:



We can get this from SAS:

data a;
     infile "./esoph.txt" firstobs=2;
     input agegp $ alcgp $ tobgp $ ncases ncontrols;
run;

proc print data=a;
run;


proc summary data=a ;
     class alcgp tobgp;
     var ncases ncontrols;
     output out=temp(drop=_freq_) sum=;
run;

proc print data=temp;
run;

proc sort data=temp;
     by alcgp;
run;

data final(drop=_type_);
     merge temp(where=(_type_=3)) temp(where=(_type_=2) rename=(ncases=tot_ncases ncontrols=tot_ncontrols));
     by alcgp;
run;

proc print data=final;
run;

The output is:

proc surveyselect method=pps: repeatedly sample if sample size is greater than ops maximum available sample size

*** when use pps in proc surveyselect, there is the limitation of maximum sample size (say 1000) every time. If your wanted sample size (say 3000) ***;

*** is greater than this max available sample size, it will show error message. One way to solve this is to sample several times (3 times) with ***;

*** each time to sample the max available sample size. That is, sample 3 times without replacement with each time 1000 samples ***;

%macro ppssample(dataname, m_tot_sample_size, sizevar);
proc sql;
 select floor(sum(&sizevar)/max(&sizevar)) into: m_max_sample_size from &dataname;
quit;

data _null_;
       pps_loop=ceil(&m_tot_sample_size/&m_max_sample_size);
       pps_each_size=ceil(&m_tot_sample_size/pps_loop);
       call symput('pps_loop',pps_loop);
       call symput('pps_each_size', pps_each_size);
run;

%put m_max_sample_size=&m_max_sample_size;

data first_sample;
 set &dataname;
run;

%if &pps_loop>1 %then %do;

 %do i=1 %to &pps_loop;

  proc surveyselect data=first_sample  method=pps sampsize=&pps_each_size
   out=pps_sample_&i. seed=-1;
   size &sizevar;
  run;
  title "to compare: first_sample";
  proc contents data=first_sample;
  run;
  title "to coampare: pps_sample_&i.";
  proc contents data=pps_sample_&i.;
  run;

  proc sql;
   create table first_sample as
   select * from first_sample
   EXCEPT CORR
   select * from pps_sample_&i.(drop=samplingweight SelectionProb);
  quit;

                      title "contents of first_sample for loop &i";
                      proc contents data=first_sample;
                      run;

  data pps_sample;
   %if &i=1 %then %do;
    set pps_sample_1;
   %end;
   %else %do;
    set pps_sample pps_sample_&i.;
   %end;
  run;
              %end;

      %end;

      %else %do;

              proc surveyselect data=first_sample method=pps sampsize=&m_tot_sample_size out=pps_sample seed=-1;
     size &sizevar;
  run;

      %end;

proc surveyselect data=pps_sample method=srs n=&m_tot_sample_size out=final;
run;

%mend ppssample;

%ppssample(prep,5000,revenue);

function fileexist to determine a file exist or not

in linux, the filename is case sensitive and remember to add surfix .

data a;
a=fileexist("/data02/temp/temp_hsong/seo/seo_expr/seo_visitnew_raw_incr.sas7bdat");
run;

proc print data=a;
run;


Obs a

1 1

IS MISSING / IS NULL to select missing rows

In sas, if want to select those missing rows, you can use VAR=. (for numeric var) or VAR='' OR VAR=' ' (for char var).

A convenient way is to use VAR IS MISSING   OR   VAR IS NULL.

Using Multiple SET Statements

When you use multiple SET statements,

• processing stops when SAS encounters the end-of-file (EOF) marker on either data set (even if there is more data in the other data set) 
•  
• the variables in the program data vector (PDV) are not reinitialized when a second SET statement is executed. 

This is useful when want to combine the summary info with the details rows of the data set.

suppose sasuser.summary has one var named sum, with one obs valued as 100; sasuser.monthsum has two vars: month sale:
month sale
1      28
2      32
3      40
we want to combine them together to calculate the percentage for each month.

data a; 

  if _N_=1 then set sasuser.summary;

  set sasuser.monthsum;

  pct=sale/sum;

run;

the automatic variable _N_ keeps track of how many times the DATA step has begun to execute. The following DATA step uses _N_ to keep SAS from reaching the EOF marker for Sasuser.Summary after the first iteration of the step. Since the variables in the PDV will not be reinitialized on each iteration, the first value of Summary.Cargosum will be retained in the PDV for each observation that is read from Sasuser.Monthsum. 

zz: SAS与R语言的数据加载与转化

第一：R加载/调用SAS
--在SAS中生成传送文件
LIBNAME SAS_R xport 'C:\sea.xpt';
DATA SAS_R.sea;
SET custdet1;
RUN;
--在R中读入
library(foreign)
library(Hmisc)
sea<-sasxport.get("c:/sea.xpt")
head(mydata)

第二：SAS调用R的数据
Library(foreign)
write.foreign(sea,"c:/sea.txt","c:/sea.sas",package="SAS")
在C盘中会生成二个文件：
SAS程序代码
一个是TXT文件
然后在SAS中直接加载程序就行。

zz: 如何在R中调用matlab

install.packages("R.matlab")
library(R.matlab)
path <- system.file("mat-files", package="R.matlab")
mat <- readMat(file.path(path, "structLooped.mat"))
s <- mat$s
fields <- dimnames(s)[[1]]
cat("Field names: ", paste(fields, collapse=", "), "\n", sep="");

print(s)

check difference of PROC SUMMARY <> PROC MEANS (not finished)

***  if VAR is missing, sas will drop that records when calculating mean. To include that records, we can impute that missing var by 0 ***;

data test;
  input a $ 1-3 b 4-5 ;
  cards;
  a 1
  a
  a 3
  b
  b 2
  b 1
  c 1
  c
    1
    4
  ;
run;

proc print data=test noobs;
  title "print of original data";
run;

proc summary data=test nway missing;
  var b;
  class a;
  output out=sum1(drop=_type_ _freq_) mean=;
run;

proc print data=sum1;
  title "Summary with MISSING opinion";
run;

proc summary data=test nway;
  var b;
  class a;
  output out=sum2(drop=_type_ _freq_) mean=;
run;

proc print data=sum2;
  title "Summary without MISSING opinion";
run;

proc means data=test nway missing;
  var b;
  class a;
  output out=means1(drop=_type_ _freq_) mean=;
run;

proc print data=means1;
  title "Means with MISSING opinion";
run;

proc summary data=test nway;
  var b;
  class a;
  output out=means2(drop=_type_ _freq_) mean=;
run;

proc print data=means2;
  title "Means without MISSING opinion";
run;

^LSummary with MISSING opinion

Obs    a     b

 1          2.5
 2     a    2.0
 3     b    1.5
 4     c    1.0


^LSummary without MISSING opinion

Obs    a     b

 1     a    2.0
 2     b    1.5
 3     c    1.0


^LMeans with MISSING opinion

Obs    a     b

 1          2.5
 2     a    2.0
 3     b    1.5
 4     c    1.0


^LMeans without MISSING opinion

Obs    a     b

 1     a    2.0
 2     b    1.5
 3     c    1.0

zz: proc means and proc mixed for paired t test

/*********************************************************************
FILENAME: MIXVMEAN.SAS
SUBJECT HEADING: STAT
INITIALS:  KBW
DATE:  7/26/96
PROGRAM:  SAS
VERSION:  6.11
PLATFORM:  WINDOWS 3.11 TS040
TITLE:  USING PROC MIXED FOR A PAIRED T-TEST COMPARED TO PROC MEANS

DESCRIPTION:  THIS PROGRAM DOES A PAIRED T-TEST, FOR THE SAME DATA,
              FIRST USING PROC MEANS, AND THEN USING PROC MIXED.
              NOTE THAT THE DATA STRUCTURE FOR THESE TWO METHODS
              IS DIFFERENT.  THE DATA STEPS AT THE BEGINNING SET
              UP THE TWO DIFFERENT DATA STRUCTURES.
              NOTE THAT THE T-TEST, THE DEGREES OF FREEDOM AND
              THE P-VALUES ARE THE SAME FOR BOTH METHODS.
**********************************************************************/


data test;
  input y1 y2;
  diff=y1-y2;
  Pair+1;
  datalines;
  13 15
  12 14
  17 17.2
  14 18
  11 12
  5  4.1
  7 9.3
     ;
run;

proc print data=test;
  title 'printout of original data set';
run;

proc means n mean t prt;
  var diff;
  title 'paired t-test using proc means';
run;

data test2(keep=y pair group);
  set test;
     y=y1;
     group=1;
     output;
     y=y2;
     group=0;
     output;
run;

proc print data=test2;
  title 'rearranged data for proc mixed';
run;

proc mixed;
    class pair group;
    model y=group;
    random pair;
    lsmeans group / pdiff;
    title 'paired t-test using proc mixed';
run; 

from SAS: Determining the Number of Variables and Observations in a Data Set

%macro obsnvars(ds);
   %global dset nvars nobs;
   %let dset=&ds;
   %let dsid = %sysfunc(open(&dset));
   %if &dsid %then
      %do;
         %let nobs =%sysfunc(attrn(&dsid,NOBS));
         %let nvars=%sysfunc(attrn(&dsid,NVARS));
         %let rc = %sysfunc(close(&dsid));
         %put &dset has &nvars  variable(s) and &nobs observation(s).;
      %end;
   %else
      %put Open for data set &dset failed - %sysfunc(sysmsg());
%mend obsnvars;

%obsnvars(sasuser.houses)

******************************************************************************;

%macro nobs(Dsn= /*Data set name */);
  if exist("&Dsn") then do;
    Dsid = open("&Dsn","i");
    Nobs = attrn(Dsid,"Nlobs");
  end;
  else Nobs=.;
  rc = close(Dsid);
%mend nobs; 

array with dynamic dim, array with dim number being a macro number

In the following data, group 1 is control grp and grp 2 to 6 are test grps. Each group we recorded the visits.First we want to calculate the ratio of each group v.s. group 1. After we get the ratio, we compare the ratio of each day with the ratio of the sample to check whether the ratio is higher or lower than the sample ratio.

To calculate the ratio, by intuition we will use array. Before using array, we need to transpose the data set.

proc transpose data=report_visits_h out=report_visits_v prefix=group;
run;

proc print data=report_visits_v ;
 title "-----title print of report_visits_v ";
run;
title "";

The output is like:

From the output, we should drop the first row:

proc sql;
 select count(1) into :m_n from report_visits_v;
quit;

%let n_m_n=%eval(&m_n-1);
%let m_pct=%eval(&n_m_n-1);

data report_visits_v;
 set report_visits_v;
 if _n_>=2;
 drop _name_;
run;

proc print data=report_visits_v;
 title "------title trans to horizontal";
run;
title "";

Then we can calculate the ratio, like (here use group: to list all vars whose name begin with group):

data report_visits_v;
 set report_visits_v;
 array a1{*} group: ;                   ** array with dynamic # of dim;
 array a2{*} ratio1-ratio6;
 do i=1 to dim(a1);
   a2{i}=a1{i}/a1{1};
 end;
 format ratio2-ratio6 6.5;
 drop i;
run;

proc print data=report_visits_v width=min;
run; 

The output is:

Then we transfer it back to horizontal data, that is, each row is for one group.

proc transpose data=report_visits_v out=report_visits_h2;
run;

data orig_records;
 set report_visits_h2;
 if _n_<=6 then output orig_records;
 rename _name_=group;
run;

data ratio;
 set report_visits_h2;
 if _n_>6 then output ratio;
run;

data ratio(drop=_name_);
 retain grp_over_grp1;
 set ratio;
 grp_over_grp1=substr(_name_,6,1);
run;

proc print data=ratio;
 title "------title print of ratio";
run;
title "";

Now the print of data set ratio is like:

Next we calculate the percentage of ratio changed for each day compared with the sample ratio.(if macro variable n_m_n without %eval, it will not work here since its resolution will be a character and therefore the resolution could not be used as the number of dim or to indicate how many col are there):

data pct;
 set ratio;
 pct_over_sample = '';
 array a3{*} col1-col&n_m_n;  ** compare with condition without %eval for n_m_n;
 array pct{&n_m_n} ;          ** array with dim number being a macro variable;
 do i=1 to dim(a3);
   pct{i}=(a3{i}-a3{1})/a3{1};
 end;
 format pct2-pct&n_m_n percentn12.6;
 keep pct: ;
run;

proc print data=pct;
 title "-----title print of percentage change";
run;

The output is:

That is what we want.

use array to select partial data

In the following example, suppose we wanna add 2/3 for those variables whose name begin with 'a'. This iterative work is easy to be done with array. The trick is how to select the columns with name begin with 'a';

data t1;
  input a b a1 a2 c a3;
  cards;
  1 2 3 4 5 6
  6 5 4 3 2 1
  ;
run;

proc print data=t1;
run;

data b (drop=i);
  set t1;
  array a_t{*} a: ;
  d=dim(a_t);
  do i =1 to dim(a_t);
    a_t{i}=a_t{i}+2/3;
  end;
run;

proc print data=b width=min;
run;

proc format: format characters to numbers

learn proc format:

data test;
input start $ end $ label;
cards;
nextag   nextag 1
#sina    #sina  2
#1000    #1000  3
;
run;

data fmt0;
retain fmtname'$testfmt' ;
set test end=last;
start=start;
end=end;
label=label;
output;
if last then do;
  hlo='O';
  label=0;
  output;
end;
run;

proc print data=fmt0;
run;

proc format cntlin=fmt0;
select $testfmt;
run;

data test;
input test $20.;
cards;
nextag
#sina
&ok
#1000
wokong
;
run;

data test_result;
set test;
id=put(test, $testfmt.)+0;
run;

proc print data=test_result;
run;

The output is:

Obs    test      id

 1     nextag     1
 2     #sina      2
 3     &ok        0
 4     #1000      3
 5     wokong     0

index， rename， keep

A new data set test1 is read from test(two variables: id, a). We want to create an index for test1(index name is called aid since aid is indexed in data set aid). Pay attention how index, rename and keep is used in data step test1.

data test;
 input id a;
 cards;
 1 1
 3 3
 2 5
 ;
run;

data aid (index=(aid));
 input aid b;
 cards;
 1 1
 2 3
 3 4
 ;
run;

data test1(index=(aid));
 set test(keep=id a );
 rename id =  aid;
run;

proc contents data=test1;
run;

data merge_1;
 merge aid test1;
 by aid;
run;

proc print data=merge_1;
run;

use PROC FORMAT to rank

The question is: after calculate the percentage, we want to count from how many obs are in 0-1%, how many in 1%-2%, ..., how many in 99%-100%. For this simple example, we can use round or floor or ceil to get the result. But here shows how to use proc format to get it.

After we get cumulative percentage, we format the percentage with put function. Take care in the proc format don't forget to add hlo, otherwise it will gives error since the first start number is missing.

data test;
 do i=1 to 1000;
   x=ranpoi(8,8);
   output;
 end;
run;

data test;
 set test;
 y+x;
run;

proc sql;
 create table test as
 select x, y, y/max(y) as pct
 from test;
quit;

data test2;
 do i=1 to 100;
   x=i/100;
   y=lag(x);
   output;
 end;
run;

data a;
 set test2;
 fmtname='fmttestf';
 start=y;
 end=x;
 label=i;
 eexcl='Y';
 if _n_=1 then hlo='L';
run;

proc format cntlin=a;
select fmttestf;
run;

data final;
 set test;
 rank=put(pct, fmttestf.)+0;
run;

proc print data=final;
run;

proc sql;
 select rank, count(1) as cnt
 from final
 group by rank
 order by rank;
quit;

sample from a data set with sample data sets are similar to each other

Purpose: sample 5 sub data set (each has 1000 obs) from a data set (test, which has 100000 obs). The purpose is to make sure in each sample, the mean of variable x is similar to the other samples. Here in the example set the difference of mean of x is less than .05.

data test0;
 do i=1 to 100000;
   x=ranuni(1);
   output;
 end;
run;

%let n_dataset=5;
%let m_size=1000;

%macro m_sample;
 %do j=1 %to 15;

   data test;
     set test0;
   run;

   %do i=1 %to &n_dataset;

     proc sort data=test;
       by i;
     run;

     proc surveyselect data=test %if &i>1 %then %do; (where=(group<1)) %end; out=sample_&i method=sys sampsize=1000;
     run;
     proc sort data=sample_&i;
       by i;
     run;
     data test;
       merge test sample_&i(in=in1);
       by i;
       if in1=1 then group=&i;
     run;

   %end;

   data test;
     set test;
     if group=. then group=6;
   run;

   proc summary data=test nomissing;
     class group;
     var x;
     output out=out_sum mean(x) = total;
   run;

   proc print data=out_sum width=min;
   run;

   data out_sum;
     set out_sum;
     id=1;
   run;

   data summary;
     merge out_sum(where=(_type_=1)) out_sum(where=(_type_=0) rename=(total=all_total));
     by id;
     pct_diff=abs(total/all_total-1);
     if pct_diff<.05 then flag=1;
   run;

   proc print data=summary width=min;
   run;

   proc sql;
     select sum(flag) into :flag from summary;
   quit;

   %if &flag=6 %then %do;
     endsas;
   %end;

 %end;

%mend;

%m_sample;

use SAS to read data from website: use filename url

a sample sas code:

filename testdata url "http://dl.dropbox.com/u/10684315/sas_pub/sas_read_webdata.csv";

data test;
  infile testdata dsd firstobs=2;
  length CANONICAL_KEYWORD $400.;
  input keyword_id CANONICAL_KEYWORD $ node_id rank;
run;

data test;
  set test;
  canonical_keyword=translate(canonical_keyword,' ','"');
  canonical_keyword=trim(left(canonical_keyword));
run;

proc contents data=test;
run;

proc print data=test width=min;
run;

An example code from SAS help:

filename foo url 
    'http://support.sas.com/techsup/service_intro.html';
       
data _null_;
   infile foo length=len;
   input record $varying200. len;
   put record $varying200. len;
   if _n_=15 then stop;
run; 

split a big data set into several subsets

This questions comes from when I want to email a dataset, it's too large to email. So I need to split it into several subsets to email.

Usually for two variables data, 1 million obs will be about 10MB after gzip. This is a proper size to email.

Here shows an example how to split a 10000 records data into several subdata with each having about 2300 obs.

options mprint mlogic;

data test;

  do i=1 to 10000;

    x=rannor(1);

      output;

  end;

run;

%let n_obs=2300;

proc sql noprint;

  select count(1) into :m_total from test;

quit;

%macro sub_data;

    %let n_dataset=%sysfunc(ceil((&m_total/&n_obs)));

      %do i=1 %to &n_dataset;

        data sub_data_&i;

          set test;

        if (&i-1)*&n_obs+1<=_n_<=&i*&n_obs;

        run;

      %end;

%mend sub_data;

%sub_data;

how to sample several groups of data from a given data set, sampled data will not appear again?

e.g., data test has 10000 obs, we want to sample 3 data sets, called sample_1, sample_2 and sample_3. The obs in sample_1 will not appear in sample_2, the obs in sample 1, sample_2 will not appear in sample_3.

In the following we use an indicator called group. For sample_1, we srs from data test, and id its group=1. For sample_2, and so on, we restrict group<1 to exclude data in sample_1. And id sample_2 as group=2, and so on.

options mprint mlogic;

data test;

  do i=1 to 10000;

    x=rannor(1);

      output;

  end;

run;

%let n_sample=3;

%macro m_sample;

  %do i=1 %to &n_sample;

    proc surveyselect data=test %if &i>1 %then %do; (where=(group<1)) %end; method=srs sampsize=100 out=sample_&i;

    run;

      proc sort data=sample_&i;

        by i;

      run;

      proc sort data=test;

        by i;

      run;

      data test;

        merge test sample_&i(in=in2);

        by i;

        if in2 then group=&i;

      run;

  %end;

%mend m_sample;

%m_sample;

gzip several different files in linux

e.g., during sas running, there are several output and we want to send out these different output in one email.

1: use proc printto to print to lst or print to csv files; if for lst, remember to cat xx.lst >> xx.txt for easy to read.

2: tar different files together.   x "tar -cvf  to_be_together.tar  file1.csv file2.txt file3.log"; This step to tar file1, file2, file3 together into one file called to_be_together.tar

3: x "gzip -f to_be_together.tar"

that's it.

zz: A question about how to use _N_

The original is from mysas(probably? Forgot it). Look at the answer and pay attention how to use _N_.
 

Question：

data a;

input id $ x;

cards;

a 0

a 1

a 2

a 1

b 3

c 0

c 3

d 2

d 2

e 1

;

run;

* using the data above, how to get a variable x_grp, which is made of distinct value of x in each id group. The data should be like:

id x x_grp

a 0 0_1_2

a 1 0_1_2

a 2 0_1_2

a 1 0_1_2

b 3 3

c 0 0_3

c 3 0_3

d 2 2

d 2 2

e 1 1

Answers：

Method 1：

data b;

    length x_grp $50;

    do _n_=1 by 1 until(last.id);

        set a;

        by id notsorted;

        if indexw(trim(x_grp),cats(x),'_')=0 then x_grp=catx('_',x_grp,x);

    end;

    do _n_=1 to _n_;

        set a;

        output;

    end;

run;

Method 2：

data a2;

set a;

retain grp;

by id notsorted;

if first.id then grp=x;

else if find(grp,compress(x))=0 then grp=catx('_',grp,x);

if last.id then output;

run;

data b;

merge a a2;

by id;

run;