topic Percent Missing List in SAS Programming

Percent Missing List

Davanden — Fri, 14 Apr 2017 17:18:01 GMT

I have a large survey data set with hundreds of variables, both numeric and character. Each variable can take two missing values, M or N. I want to make a list that shows the percent of M values for each variable. I can define formats to collapse all the variables into M and not-M, but I'm stuck at producing the list. With this many variables, hand-coding is out of the question. I thought to make an output dataset with PROC FREQ, but that allows only one frequency distribution per output dataset. The regular output from PROC FREQ contains all the headers, which I don't want to have to edit out. I've also tried PROC TABULATE, but I can't work out how to get it to do what I want. I could set up a macro to loop through each variable, run PROC FREQ, and concatenate the output datasets. However, I feel like there must be a smack-in-the-head-of-course method that I'm missing. What is it?

I want a list like this:

Variable Name Percent M.

--Dav

Re: Percent Missing List

ballardw — Fri, 14 Apr 2017 18:25:33 GMT

Some example data would be helpful. You may end up going back to re-reading your raw data.

SAS has more than one "missing" actually a single variable can have up to 27 different missing values.

Here's a brief example of recoding values to the missing for numeric values.

data example;
   input q1 q2 q3;
   /* assume questions q1 through 3 are coded the same
   and a recorded respose of 77 means "Don't know"
   and 99 is "Refused"*/
   array q q1-q3;
   do i=1 to dim(q);
      if q[i]= 77 then q[i]= .D;
      if q[i]= 99 then q[i]= .R;
   end;

datalines;
1 2 77
2 3 1
99 4 3
1 . 2
;
run;

proc freq data=example;
   tables q1 q2 q3 ;
run;
proc format library=work;
value myq
.D= "Don't Know"
.R= "Refused"
. = "No Answer"
;
run;

proc freq data=example;
   tables q1 q2 q3/missing ;
   format q: myq.; 
run;

proc tabulate data=example;
   class q1 q2 q3/missing;
   format q1 q2 q3 myq.;
   tables q1 q2 q3, n pctn;
run;

If you need to do the exact same thing to all of the numeric variables you can use: Array arrayname _numeric_; which places all of the numeric variables into the array.

Same with character variables:

Array charvars _character_;

For anything else you should provide some concrete input data and what the result from that data is expected.

Re: Percent Missing List

Astounding — Fri, 14 Apr 2017 19:43:44 GMT

(a) are your variables character (possibly with a value of "M") or numeric (possibly with a value of .M)?

(b) when computing percentages, do you want all non-M values (including missing values) included in the denominator?

The programming isn't that difficult, but I need the answers first.

Re: Percent Missing List

Ksharp — Sat, 15 Apr 2017 03:39:18 GMT

" both numeric and character. Each variable can take two missing values, M or N."

Show us the sample data and the output you are looking for .

data have;
input a $ b;
cards;
M 12
c .M
x .N
n .M
;
run;

data _null_;
 set sashelp.vcolumn(keep=libname memname name type
 where=(upcase(libname)='WORK' and upcase(memname)='HAVE')) end=last;
 if _n_=1 then call execute('proc sql;create table temp as select ');
 if type='char' then call execute(cat('sum(',name,'="M")/count(*) as ',name));
  else call execute(cat('sum(',name,'=.M)/count(*) as ',name));
 if last then call execute('from have;quit;');
  else call execute(',');
run;
proc transpose data=temp out=want;
run;

Re: Percent Missing List

Davanden — Mon, 17 Apr 2017 12:50:15 GMT

Thanks to everyone who replied. I am aware that SAS variables can take multiple missing values, and that there is a difference in the way they are treated for numeric an character variables. In my case, I have both character and numeric variables. For character variables, missing values are indcated by 'M' or 'N'; for numeric, .M or .N. I want to list the percentage of all cases that have the 'M' or .M value, as applicable, for every variable, of which there are a great many.

For a totally made-up example, see the pictures below:

Re: Percent Missing List

Ksharp — Mon, 17 Apr 2017 13:18:11 GMT

So did you try my code ?

Re: Percent Missing List

Davanden — Mon, 17 Apr 2017 13:41:45 GMT

Yes, I did. It does work. Thank you for your help. My pride is assuaged by the fact that it was not as simple and obvious as I had feared.

--Dav

Re: Percent Missing List

Ksharp — Tue, 18 Apr 2017 04:52:27 GMT

if you want simpler, IML code might do it .