David,

Instead of 'collapse' the result of proc summary, I would rather 'transpose' the raw data first, then a proc freq will get what you want:

options nocenter; data a; var1 = 'a1'; var2 = ' '; var3 = 1; var4 = 'g'; output; var1 = 'a2'; var2 = ' '; var3 = 2; var4 = 'j'; output; var1 = ' '; var2 = ' '; var3 = 3; var4 = 'i'; output; var1 = 'a4'; var2 = ' '; var3 = 3; var4 = 'h'; output; var1 = 'a5'; var2 = ' '; var3 = 2; var4 = 'g'; output; var1 = 'a1'; var2 = ' '; var3 = .; var4 = ' '; output; var1 = ' '; var2 = ' '; var3 = 2; var4 = 'j'; output; var1 = 'a3'; var2 = ' '; var3 = .; var4 = ' '; output; run;

data a(where=(vnm ne 'class')); set a; length class $20; array ch _character_; array num _numeric_; do over ch; vnm=vname(ch); class=ch; output; end; do over num; vnm=vname(num); class=left(put(num,best20.)); output; end; keep vnm class; run;

proc freq noprint; table class*vnm / out=b (drop=percent); run;

proc print; run;

-------------- Obs class vnm COUNT

1 var1 2 2 var2 8 3 var4 2 4 . var3 2 5 1 var3 1 6 2 var3 3 7 3 var3 2 8 a1 var1 2 9 a2 var1 1 10 a3 var1 1 11 a4 var1 1 12 a5 var1 1 13 g var4 2 14 h var4 1 15 i var4 1 16 j var4 2

Kind regards,

Ya Huang -----Original Message----- From: Boylan, David [mailto:DBoylan@HRBLOCK.COM] Sent: Wednesday, December 11, 2002 8:09 AM To: SAS-L@LISTSERV.UGA.EDU Subject: collapse columns in a proc means output (was:RE: resolve macro v ariable to value of sas variable)

Ron Fehd asked, "show us an example of what you think you're trying to do and we'll critique from there." Well, great idea Ron. Instead of requesting specific info, I'll post the problem I'm trying to solve. I'm sure I'll get some very interesting solutions.

I'm trying to summarize a number of classification columns in a large database. Using the types statement (along with class )in proc means allows me to get one way summary level classifications for a number of columns in just one pass (important). The means output essentially stacks the one way summaries on top of each other, where the relevant classification info is in its associated column. That is, there is one column for each variable in the class list.

What I want, however, is to collapse all of those class columns into one column and add another column that indicates the name of the variable that was classified. An example my better illustrate what I'm trying to do:

data a; var1 = 'a1'; var2 = ' '; var3 = 1; var4 = 'g'; output; var1 = 'a2'; var2 = ' '; var3 = 2; var4 = 'j'; output; var1 = ' '; var2 = ' '; var3 = 3; var4 = 'i'; output; var1 = 'a4'; var2 = ' '; var3 = 3; var4 = 'h'; output; var1 = 'a5'; var2 = ' '; var3 = 2; var4 = 'g'; output; var1 = 'a1'; var2 = ' '; var3 = .; var4 = ' '; output; var1 = ' '; var2 = ' '; var3 = 2; var4 = 'j'; output; var1 = 'a3'; var2 = ' '; var3 = .; var4 = ' '; output; run;

%let cvars = var1 var2 var3 var4; proc summary data=a nway noprint missing chartype; class &cvars; types &cvars; output out=sum1; run;

The output in the sum1 dataset gets me half way there. I now need to do step II and collapse all of the classification columns into one column. Something like the following:

classification varname _freq_ var4 2 g var4 2 h var4 1 i var4 1 j var4 2 . var3 2 1 var3 1 2 var3 3 3 var3 2 . var2 8 var1 2 a1 var1 2 a2 var1 1 a3 var1 1 a4 var1 1 a5 var1 1

I don't want to hard code the column names because it is a long and continuously changing list. I'll have the column names on a macro variable called &cvars. Note also that the &cvars list will represent both character and numeric columns.

Any ideas out there?

Thanks, Dave