Re-arranging output of proc freq

Reply
Frequent Contributor
Posts: 94

Re-arranging output of proc freq

I would like to analyse given data set and display number of complete/missing observations so that they had the structure as can be seen in the attached picture:

 

Capture.PNG

 

The code I am trying to amend to get such output is here:

 

data test;
do i = 1 to 100;
output;
end;
run;

data test1;
b = .;
run;

data test2;
c = '';
D = '';
run;

data fin;
set test test1 test2;
run;

proc format;
	value $missfmt ' '='Missing' other='Not Missing';
	value missfmt .='Missing' other ='Not Missing';
run;

ODS OUTPUT ONEWAYFREQS=TMP;
proc freq data=fin;
	format _CHAR_ $missfmt.;
	table _CHAR_ / missing missprint nocum NOPERCENT;
	format _NUMERIC_ missfmt.;
	tables _NUMERIC_ / missing missprint nocum NOPERCENT;
run;

 

 

I would also like to know whether it is more efficient to write my own sql query or simply use the proc freq for it (the data set is huge: ~75 mil rows and 100 variables).

Any suggestion how to get the result I am after? Thanks!

 

Trusted Advisor
Posts: 1,826

Re: Re-arranging output of proc freq

Posted in reply to Uknown_user

I believe that proc freq is the most efficient tool to get the wanted output.

You can add the OUTPUT option to get the output into a sas dataset, then

you can create your own report in a desired format.

 

proc freq data=fin;
	format _CHAR_ $missfmt.;
	table _CHAR_ / missing missprint nocum NOPERCENT;
	format _NUMERIC_ missfmt.;
	tables _NUMERIC_ / missing missprint nocum NOPERCENT;
  
       OUTPUT OUT=<dataset name>;

run;
Super User
Posts: 22,874

Re: Re-arranging output of proc freq

Posted in reply to Uknown_user

The code below creates a summary data set for reports with percentages. It should give you the idea on how to modify your own process, you can switch the variables to be the count variable you're interested in. 

 

https://gist.github.com/statgeek/2de1faf1644dc8160fe721056202f111

 

 

Frequent Contributor
Posts: 94

Re: Re-arranging output of proc freq

Thanks, will take a look at it.

Super User
Posts: 10,623

Re: Re-arranging output of proc freq

Posted in reply to Uknown_user
That would be a lot easy for SQL.

data test;
do i = 1 to 100;
output;
end;
run;

data test1;
b = .;
run;

data test2;
c = '';
D = '';
run;

data fin;
set test test1 test2;
run;

data _null_;
 set sashelp.vcolumn(where=(libname='WORK' and memname='FIN')) end=last;
 if _n_=1 then call execute('proc sql; ');
 call execute(catt('select "',name,'" as variable length=40,count(*) as nobs,',
 ' nmiss(',name,') as nmissing from WORK.FIN'));
 if last then call execute(';quit;');
  else call execute('union');
run;


Ask a Question
Discussion stats
  • 4 replies
  • 134 views
  • 0 likes
  • 4 in conversation