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.

No comments:

Post a Comment