Re-arranging output of proc freq

[Uknown_user]

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!

[Shmuel]

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;

[Reeza]

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

[Uknown_user]

Thanks, will take a look at it.

[Ksharp]

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;