Showing posts with label _N_. Show all posts
Showing posts with label _N_. Show all posts

Tuesday, April 17, 2012

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.

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)