Proc univariate combining

[Maisha_Huq]

Hi everyone,

How could I combine multiple proc univariate output datasets where each dataset is for a different BY (statement) variable?  I d like to have one combined data set where the first column is for the different levels of each BY variable.

Desired output:

                                    Mode Mean Min Mix

BY var1 - level 1

BY var 1 - level 2

BY var 2 - level 1

BY var 2 - level 2

BY var 2 - level 3

Current Output:

ByVar 1                            BYvar2                                 Mode Mean Min Mix

BY var1 - level 1                      .

BY var 1 - level 2                     .

.                                   BYvar2 - level 1

.                                   BYVar2  - level 2     

.                                   BYvar2 - level3           

 Current code:

%macro univariate(input, byvar, var, wgt, odata, num, nused);

proc sort data=&input; by &byvar; run;

proc univariate data=&input ;

var &var;

weight &wgt;

by &byvar;

output out=st_&var.&odata mean=Mean mode=Mode min=Minimum max=Maximum NMiss=Missing N=Ns;

run;

data st_&var.&odata;

set st_&var.&odata;

/*row=&num.;*/

/*row+1;*/

nused="&nused.";

N_=Ns+Missing;

rename N_=N;

drop missing Ns N_;

proc print data=st_&var.&odata;

run;

%mend;

[Reeza · Accepted solution]

You're probably best off cleaning it up in an additional data step using a coalesce function. You also don't need a macro if you use the CLASS and WAYS statement.

proc means data=sashelp.class stackods mode mean n min max;
class sex age;
ways 1;
var weight height;
ods output summary=want;
run;


data want;
length var $200.;
set want;
var=coalescec(sex, age);
drop sex age;
run;

[Maisha_Huq]

Thanks so much, Reeza - for also pointing out using proc means this way. Do you know if there's a way to specify what order to coalesce? In other words, can I specify a certain order for the by variables' valid values?

[Reeza]

Coalesce operates across the column, I assume your referring to the order of the rows?

The standard way is to use a format to control the order of the variable output.