Based on your data demo_data, I built an 'elegant' macro to give you the desired output dataset. The basic idea is to 1) use sas sql to count the number of non-missing values; 2) use union to concatenate the names and the non-missing values; 3) use macro and do-loop to go through the variable list and repeat the step1 and 2. So, as shown in the codes, I first created the variable list, and then calculate the length of the list that will be used for do-loop based on the position of each variable. With the help of do-loop, I combined each variable name and its non-missing value into one dataset, starting from the second variable (that's why I used 'if then' to control the loop). And voila. below is the output dataset. Non-missing value dataset %let varlist=Make#Model#Type#Origin#DriveTrain#MSRP#Invoice#EngineSize#Cylinders#Horsepower#MPG_City#MPG_Highway#Weight#Wheelbase#Length;
%macro nmiss;
%let varnum=%eval(1+(%length(%sysfunc(compbl(&varlist))) - %length(%sysfunc(compress(&varlist,'#')))));
proc sql;
create table nmiss as
%do i= 1 %to &varnum.;
%if &i ne 1 %then union;
select "%scan(&varlist.,&i,'#')" as name, count(%scan(&varlist.,&i,'#')) as num_not_missing
from demo_data
%end;
;
quit;
%mend;
%nmiss; Well, if you are not comfortable with macro, you can also use the following codes in sas sql. The coding could be long depending on the number of the variables, but the idea is really straightforward. For example: select 'Make' as name, count(Make) as num_not_missing from demo_data union select 'Model' as name, count(Model) as num_not_missing from demo_data union ...
... View more
