topic Re: Multiple by variables in proc means in SAS Programming

Multiple by variables in proc means

mgm — Thu, 15 Sep 2016 13:53:25 GMT

I would like to rank across multiple grouping variables independently and then combine the results. Is there an easy way to do it (outside of using macros) .

proc RANK data = a ; 
by var1;
output out  = t1 mean(ret);
run;

proc RANK data = a ; 
by var2;
output out  = t2 mean(ret);
run;

and then combine t1 and t 2 . ...

Re: Multiple by variables in proc means

RW9 — Thu, 15 Sep 2016 14:05:18 GMT

Please post test data (as a datatstep) an drequired output, it really helps illustrate what you want. I imageine its something along the lines of:

VAR1 V1RANK VAR2 V2RANK

ABC 1 DEF 1

ABC 1 RFG 2

GHJ 2 XYZ 1

If so you can sort the data, and retain a count for each by group - in the above case:

data want;
  set have;
  retain v1rank v2rank;
  by var1 var2;    /* Assumes sorted */
  if first.var1 then v1rank=sum(v1rank,1);
  if first.var2 then v2rank=sum(v2rank,1);
run;

Re: Multiple by variables in proc means

mgm — Thu, 15 Sep 2016 14:19:51 GMT

Sorry I botched up my request earlier.

Here is the sample data :

rownum	Rank1	Rank 2	Var1
1	1	3	1.2
2	1	4	1.1
3	2	5	0.9
4	3	1	0.766667
5	4	2	0.616667
6	4	1	0.466667
7	4	2	0.316667
8	5	2	0.166667
9	2	1	0.016667
10	4	4	-0.13333
11	5	5	-0.28333
12	4	5	-0.43333
13	3	2	-0.58333

Output :

Ranks	Average of Var1 by RAnk1	Average of Var1 by RAnk2
1	1.15	0.417
2	0.46	0.129
3	0.09	1.2
4	0.17	0.483
5	-0.06	0.061

Proc rank should read proc means earlier.

Re: Multiple by variables in proc means

Loko — Thu, 15 Sep 2016 14:43:56 GMT

hello,


data have;
infile datalines;
input rownum Rank1 Rank2	Var1;
datalines;
1 1 3 1.2
2 1 4 1.1
3 2 5 0.9
4 3 1 0.766667
5 4 2 0.616667
6 4 1 0.466667
7 4 2 0.316667
8 5 2 0.166667
9 2 1 0.016667
10 4 4 -0.13333
11 5 5 -0.28333
12 4 5 -0.43333
13 3 2 -0.58333
;

proc means data=have ;
class Rank1 Rank2;
Var  Var1;
ways 1;
output out=int(drop=_type_ _freq_) mean()=;
run;

data want;

set int(drop=rank2 rename=(var1=Average_of_Var1_by_RAnk1 rank1=rank) where=(missing(rank)=0) );
set int(drop=rank1 rename=(var1=Average_of_Var1_by_RAnk2 rank2=rank) where=(missing(rank)=0) );

run;

Re: Multiple by variables in proc means

mgm — Thu, 15 Sep 2016 15:22:06 GMT

Thanks it seems to work

But I was wondering if there is a scalable solution ? I have almost 20 Class variables So I would have to repeat this statement 20 times with a drop list that doesn't contain one variable at a time...

set int(drop=rank2 rename=(var1=Average_of_Var1_by_RAnk1 rank1=rank) where=(missing(rank)=0) );

Re: Multiple by variables in proc means

ballardw — Thu, 15 Sep 2016 17:54:10 GMT

The question may be do you want dataset that feeds into some other analysis or is this a report?

if a report some thing like this perhaps:

data have;
infile datalines;
input rownum Rank1 Rank2	Var1;
datalines;
1 1 3 1.2
2 1 4 1.1
3 2 5 0.9
4 3 1 0.766667
5 4 2 0.616667
6 4 1 0.466667
7 4 2 0.316667
8 5 2 0.166667
9 2 1 0.016667
10 4 4 -0.13333
11 5 5 -0.28333
12 4 5 -0.43333
13 3 2 -0.58333
;
run;

data trans (keep=Var1 RankVar RankVal);
   set have;
   array r rank: ;
   do _i_ = 1 to dim(r);
      if not missing (r(_i_)) then do;
         Rankvar = cat('Rank',_i_);
         RankVal = r(_i_);
         output;
      end;
   end;
run;

proc tabulate data=trans;
   class RankVar RankVal;
   var Var1;
   table RankVal='',
         RankVar='Rank Variable'*Var1*mean
         /box='Ranks'
   ;
run;

Re: Multiple by variables in proc means

Ksharp — Fri, 16 Sep 2016 03:00:16 GMT

I think you need make a macro like CALL EXECUTE():


data have;
infile datalines;
input rownum Rank1 Rank2	Var1;
datalines;
1 1 3 1.2
2 1 4 1.1
3 2 5 0.9
4 3 1 0.766667
5 4 2 0.616667
6 4 1 0.466667
7 4 2 0.316667
8 5 2 0.166667
9 2 1 0.016667
10 4 4 -0.13333
11 5 5 -0.28333
12 4 5 -0.43333
13 3 2 -0.58333
;
run;
proc sql;
create table temp as
 select rank1 as r,mean(var1) as mean,'rank1' as id length=20
  from have
   group by rank1
union all
 select rank2 as r,mean(var1) as mean,'rank2'
  from have
   group by rank2
order by r;
quit;
proc transpose data=temp out=want;
 by r;
 id id;
 var mean;
run;