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:

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;

data set match merge, an example to study zz

The original post want to match merge A and B, and then got the result as below:

X   Y         Z               flag
1   red      brown       0
1   red      red            1
1   blue     brown       0
1   blue     red            0
3   yellow  red            0
3   yellow  pink           0
3   green   red            0
3   green   pink           0

In proc sql, it's not difficult to use inner join to merge A and B conditional on a.x = b.x. And we need to assign value to flag as if a.y matched by b.z.  At last the program can be down as:

data a;
 input x y $;
 cards;
 1 red
 1 blue
 3 yellow
 3 green
 ;
run;

data b;
 input x z $;
 cards;
 1 brown
 1 red
 2 yellow
 3 red
 3 pink
 ;
run;

proc sql;
   select a.x, a.y, b.z, case when a.y = b.z then 1 else 0 end as flag 
   from a, b
   where a.x=b.x;
quit;

***************************************************************************

 Somebody gives the solution with data step. It's pretty good, as following:

 data c;
 set a;
 do i = 1 to num;
  set b(rename = (x = x2)) nobs = num point = i;
  output;
 end;
run;

data want (drop = x2);
 set c;
 if y = z then flag = 1;
 else flag = 0;
 where x = x2;
run;

To make it clearer, here, the print of data set c is:

                                 Obs    x    y         x2    z

                                    1    1    red        1    brown
                                    2    1    red        1    red
                                    3    1    red        2    yellow
                                    4    1    red        3    red
                                    5    1    red        3    pink
                                    6    1    blue       1    brown
                                    7    1    blue       1    red
                                    8    1    blue       2    yellow
                                    9    1    blue       3    red
                                   10    1    blue       3    pink
                                   11    3    yellow     1    brown
                                   12    3    yellow     1    red
                                   13    3    yellow     2    yellow
                                   14    3    yellow     3    red
                                   15    3    yellow     3    pink
                                   16    3    green      1    brown
                                   17    3    green      1    red
                                   18    3    green      2    yellow
                                   19    3    green      3    red
                                   20    3    green      3    pink