Sunday, January 15, 2012

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;