topic Re: Re-arranging output of proc freq in SAS Programming

Re-arranging output of proc freq

Uknown_user — Fri, 20 Oct 2017 21:26:12 GMT

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:

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!

Re: Re-arranging output of proc freq

Shmuel — Fri, 20 Oct 2017 23:52:04 GMT

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;

Re: Re-arranging output of proc freq

Reeza — Sat, 21 Oct 2017 00:31:30 GMT

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

Re: Re-arranging output of proc freq

Uknown_user — Sat, 21 Oct 2017 09:52:55 GMT

Thanks, will take a look at it.

Re: Re-arranging output of proc freq

Ksharp — Sat, 21 Oct 2017 12:18:27 GMT

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;