/* create sample data */
data one;
input a $ b $ c $ d e;
cards;
a . a 1 3
. b . 2 4
a a a . 5
. . b 3 5
a a a . 6
a a a . 7
a a a 2 8
;
run;
/* create a format to group missing and non-missing */
proc format;
value $missfmt ' '='missing'
other='non-missing';
value missfmt .='missing'
other='non-missing';
run;
%macro lst(dsn);
/** open dataset **/
%let dsid=%sysfunc(open(&dsn));
/** cnt will contain the number of variables in the dataset passed in **/
%let cnt=%sysfunc(attrn(&dsid,nvars));
%do i = 1 %to &cnt;
/** create a different macro variable for each variable in dataset **/
%let x&i=%sysfunc(varname(&dsid,&i));
/** list the type of the current variable **/
%let typ&i=%sysfunc(vartype(&dsid,&i));
%end;
/** close dataset **/
%let rc=%sysfunc(close(&dsid));
%do i = 1 %to &cnt;
/* loop through each variable in PROC FREQ and create */
/* a separate output data set */
proc freq data=&dsn noprint;
tables &&x&i / missing out=out&i(drop=percent rename=(&&x&i=value));
format &&x&i %if &&typ&i = C %then %do; $missfmt. %end;
%else %do; missfmt. %end;;
run;
data out&i;
set out&i;
varname="&&x&i";
/* create a new variable that is character so that */
/* the data sets can be combined */
%if &&typ&i=N %then %do;
value1=put(value, missfmt.);
%end;
%else %if &&typ&i=C %then %do;
value1=put(value, $missfmt.);
%end;
drop value;
rename value1=value;
run;
%end;
data combine;
set %do i=1 %to &cnt;
out&i
%end;;
run;
proc print data=combine;
run;
%mend lst;
%lst(one)
/* another way to reshape the COMBINE data set */
proc transpose data=combine out=out(drop=_:);
by varname;
id value;
var count;
run;
proc print data=out;
run;
Original output:
COUNT varname value
2 a missing
5 a non-missing
2 b missing
5 b non-missing
1 c missing
6 c non-missing
3 d missing
4 d non-missing
7 e non-missing
Transposed output:
varname missing non_missing
a 2 5
b 2 5
c 1 6
d 3 4
e . 7
Source: http://support.sas.com/kb/44/124.html
