Showing posts with label retain. Show all posts
Showing posts with label retain. Show all posts

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;

Wednesday, November 16, 2011

Did you find the difference?

Look at the differences between data set a1, a2 and a3:

data a00;
      input a b;
      cards;
      2 2
      3 3
      5 5
      ;
run;

data a01;
      x=1;
run;

data a1;
      if _n_=1 then do;
            set a01;
      end;
      set a00;
run;

data a2;
      if _n_=1 then do;
            x=1;
      end;
      set a00;
run;

data a3;
      retain x;
      if _n_=1 then do;
            x=1;
      end;
      set a00;
run;
 Things to remember:

1)  a1 v.s. a2: in " if then do end", we use set a01, the value of x will be retained in each iteration of data steps; if you put 'set' in data statement, the value will be retained automatically;

2) a2 v.s. a3: if the difference of use retain or not. use retain, the value of x is retained in each data step;

3) a1 v.s. a3: it looks there is no difference. however, the internal is different. if you remove set a00 both and you will find the new a1 has two obs while new a3 only has one obs.

step-by-step study of:
data a1;
      if _n_=1 then do;
            set a01;
      end;
      set a00;
run;


1) first step of data a1: _n_=1, read in set a01 and retain the value of x=1; goes down and read the first value of set a00; combined together we get  (x a b)=(1 2 2)
2) second step of a1: _n_=2, without reading set a01 but just the second row of a00, since x=1 is retained, so we get (x a b)
               (1 2 2)
               (2 3 3) 
3) in the same way for _n_=3;
4) forth step of data a1, _n_=4, but since a00 has only three records, jump out of data step; at last we got three rows as the result shows
5) if there is no set a00, then how many obs will we get? (the answer is 2)

Friday, November 11, 2011

retain in SAS

1: keep variable's value from being initialized when reading data in data step
2: assign initial values for variables
3: rearrange the orders of variables

e.g.1:  we want to calculate the cumulative sum of transactions for different type, and then output the cumulative sum at the last observation of type.
In order to calculate the cumulative sum, one way is to use sum statement which will ignore missing values; here we use sum function, so we need to temporarily keep the value of total within each type (e.g., A). When we reach the last obs of type (A), output the data; then set total to 0 to be ready for next type (B) cumulative sum calculation.



data test;
      set sasuser.credit;
      num_trans=input(transaction, 6.2); /* Transfet char to num */
      keep   type num_trans;
run;

proc sort data=test;
      by type;
run;

proc contents data=test;
run;

data sum_by_type;
      set test;
      by type;
      retain total;
      total = sum(total , num_trans);     /* total=total+num_trans doesn't work */
      if last.type then do;
            output;     /* Must output first then set total = 0 */
            total=0;  /* Otherwise total will be 0 rathen than cummulative sum */
      end;
run;

data sum_by_type;
      set test;
      by type;
      retain total;
      total = sum(total , num_trans);     /* total=total+num_trans doesn't work */
      if last.type then do;
            total=0;     /* If put total = 0 first, the result will be: */
            output;
      end;
run;

e.g.2: re-arrange the order of different variables: for example, there are many variables, we want to re-order them, making categorical variables list together, numeric variables together. Then we can use retain to change their orders:
Look at original variables order in sasuser.admit, they are:

Then we change the order and let char first, numeric variables next; it is:

data neworder;
      retain id actlevel name sex age date fee height weight;
      set sasuser.admit;
run;