Discover More Tips and Techniques on This Blog

Counting the number of missing and non-missing values for each variable in a data set.

/* 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

Disclosure:

In the spirit of transparency and innovation, I want to share that some of the content on this blog is generated with the assistance of ChatGPT, an AI language model developed by OpenAI. While I use this tool to help brainstorm ideas and draft content, every post is carefully reviewed, edited, and personalized by me to ensure it aligns with my voice, values, and the needs of my readers. My goal is to provide you with accurate, valuable, and engaging content, and I believe that using AI as a creative aid helps achieve that. If you have any questions or feedback about this approach, feel free to reach out. Your trust and satisfaction are my top priorities.