topic Re: macro to check percentage of missing values in a dataset in SAS Programming

macro to check percentage of missing values in a dataset

chennupriya — Tue, 11 Aug 2020 21:31:59 GMT

Hi ,

I wanted to check percentage of missing values in a dataset by variable Category . I have used below code from other forum it worked and now i wanted same by variable Category (which has values A,b,C).

proc format;

value $ missfmt ' '="Missing"

other="Not Missing"

;

value nmissfmt . ="Missing"

other="Not Missing"

;

run;

data have;

set have;

format _numeric_ nmissfmt. _character_ $missfmt.;

run;

proc freq data=have;

run;

But here's my macro. It needs work since I chopped it together from another macro I use, so not all the code in here is actually required.

%macro sum_missing(libname, dsetin, dsetout);

*Delete old dataset;

proc datasets nodetails nolist;

delete &dsetout;

quit;

*Upcase all macro variables to have consistency;

data _null_;

call symput ("libname", put(upcase("&libname."), $8.));

call symput ("dsetin", put(upcase("&dsetin."), $32.));

run;

*Formats for missing for character and numeric variables;

proc format;

value $ missfmt ' '="Missing"

other="Not Missing"

;

value nmissfmt . ="Missing"

other="Not Missing"

;

run;

*Copy dataset and apply missing formats to it;

data step1;

set &libname..&dsetin;

format _character_ $missfmt. _numeric_ nmissfmt.;

run;

options mprint symbolgen;

*Get variable list;

proc sql noprint;

select name into : var_list separated by " "

from dictionary.columns

where upcase(libname) ="&libname"

and upcase(memname)="&dsetin";

*Get count of total number of observations into macro variable;

select count(*) into : obs_count

from &libname..&dsetin.;

quit;

*Start looping through the variable list here;

%let i=1;

%do %while (%scan(&var_list, &i, " ") ^=%str());

%let var=%scan(&var_list, &i, " ");

*Get format of missing;

data _null_;

set step1;

call symput("var_fmt", vformat(&var));

call symput("var_label", vlabel(&var));

run;

*Get count of missing;

proc freq data=step1 noprint;

table &var/missing out=cross_tab1;

run;

data cross_tab2;

set cross_tab1;

length variable $50.;

category=put(&var, &var_fmt.);

variable="&var_label.";

if _n_=1 and category='Not Missing' then do;;

Number_Missing=&obs_count-count;

Percent_Missing=Number_Missing/&obs_count.;

percent=percent/100;

output;

end;

else if _n_=1 and category='Missing' then do;

Number_Missing=count;

Percent_Missing=percent/100;

output;

end;

format percent: percent10.1;

keep variable Number_Missing Percent_Missing;

run;

proc append base=&dsetout data=cross_tab2 force;

run;

proc datasets nodetails nolist;

delete cross_tab1 cross_tab2;

run; quit;

*Increment counter;

%let i=%eval(&i+1);

%end; *Categorical;

proc datasets nodetails nolist;

delete step1;

run; quit;

%mend;

Can anyone please help ?

Re: macro to check percentage of missing values in a dataset

PaigeMiller — Tue, 11 Aug 2020 21:47:38 GMT

What is wrong with the code you are using?

Why does this have to be a macro anyway? The non-macro code at the top of your message seems like it will work on any arbitrary data set.

Re: macro to check percentage of missing values in a dataset

chennupriya — Tue, 11 Aug 2020 22:00:27 GMT

Hi ,
I wanted to check how do i apply the code to check percentage missing of all variables by Category A Category B and Category C

Thanks

Re: macro to check percentage of missing values in a dataset

Reeza — Tue, 11 Aug 2020 22:06:36 GMT

Your macro seems very similar to mine....including the name....https://gist.github.com/statgeek/c3a9ddcb002c469e9d61

I've seen modified my approach as well and now use the one illustrated here. You'd need to change it to handle a two way table, since the original is designed for one way tables but that's easy if you understand what's happening.

https://gist.github.com/statgeek/2de1faf1644dc8160fe721056202f111

Re: macro to check percentage of missing values in a dataset

Watts — Tue, 11 Aug 2020 22:11:43 GMT

proc freq data=have nlevels;
     by category;
     tables _all_ / missing;
run;

Re: macro to check percentage of missing values in a dataset

ballardw — Tue, 11 Aug 2020 22:13:31 GMT

@chennupriya wrote:
Hi ,
I wanted to check how do i apply the code to check percentage missing of all variables by Category A Category B and Category C

Thanks

What is a Category A (or Category B and Category C)? Is a variable named "Category" that sometimes has values of A?

In which case your requirement is very unclear as when Category=A it cannot be missing.

Or are we supposed to identify which variables are involved because they sometimes have values of A, B or C?

Or do you have a document somewhere that says variables AAA, PDQ and XYZ represent category A?

Re: macro to check percentage of missing values in a dataset

chennupriya — Tue, 11 Aug 2020 22:18:07 GMT

wanted percenatges of XX , YY by Category

XX YY Category
100 . A
200 20 A
300 30 B
400 . C
500 . C

Re: macro to check percentage of missing values in a dataset

chennupriya — Tue, 11 Aug 2020 22:24:36 GMT

Thank you so much for the reply . Below is the output .But can i get same layout by variable Sex ?

variable N_Missing N_Not Missing PCT_Missing PCT_Not Missing Missing Not Missing
Age 1 18 5.26 94.74 1 ( 5.3%) 18 ( 94.7%)
Height 4 15 21.05 78.95 4 ( 21.1%) 15 ( 78.9%)
Name 19 100.00 19 ( 100%)
Sex 4 15 21.05 78.95 4 ( 21.1%) 15 ( 78.9%)
Weight 4 15 21.05 78.95 4 ( 21.1%) 15 ( 78.9%)

Re: macro to check percentage of missing values in a dataset

PaigeMiller — Tue, 11 Aug 2020 22:36:07 GMT

@chennupriya wrote:

I wanted to check how do i apply the code to check percentage missing of all variables by Category A Category B and Category C

The first step in any macro writing ought to be to create working SAS code without macros and without macro variables, for a single data set. Once you have done that, turning the results into a macro will be relatively simple. But it doesn't appear you have done that yet.

Re: macro to check percentage of missing values in a dataset

ballardw — Tue, 11 Aug 2020 22:44:49 GMT

That could look something like:

Proc tabulate data=have;
   class category;
   class xx yy/ missing;
   format xx yy nmissfmt. ;
   tables category, 
          (xx yy)*rowpctn
         /
   ;

run;

The output table would have a row for each value of Category and a column for "Missing" and "Not missing" for XX and YY and a percentage, The ROWPCTN is a percentage of the "row", the values of XX and YY separately. The format makes two columns. The default column heading will include the statistic ROWPCTN. Tabulate supports other percentages such as PCTN, percent of all the row/col combinations of variables crossing, COLPCTN which would be the Category percentage for each value of XX or YY.

Note: By default Proc Tabulate will remove any record with a missing value for any of the CLASS variables. So we have two class statements. If Category has missing values the records would be removed and if you want them you would add the / missing option to keep them in the table as well.

Re: macro to check percentage of missing values in a dataset

Reeza — Tue, 11 Aug 2020 22:46:20 GMT

Of course, modify the macro or code to handle a grouping variable. This does mean customizing the code.

This can get you started but I don't have time to finish it up or generalize it at this point.

data class;
set sashelp.class;
if age in (13, 14) then call missing(name, weight, height);
run;
proc format;

value $ missfmt ' '="Missing"

other="Not Missing"

;

value nmissfmt . ="Missing"

other="Not Missing"

;

run;

 
ods output crosstabfreqs=summary;
proc freq data=class;
table sex*(name weight height age) / missing;
format _numeric_ nmissfmt. name $missfmt.;
run;




data long;
	length variable $32. variable_value $50.;
	set summary;
	
	if missing(sex) then delete;
	
	Variable=scan(table, 2, '*');
	Variable_Value=strip(trim(vvaluex(variable)));
	presentation=catt(frequency, " (", trim(put(percent/100, percent7.1)), ")");
	keep sex variable  variable_value frequency percent presentation;
	label variable='Variable' variable_value='Variable Value';
run;

Re: macro to check percentage of missing values in a dataset

chennupriya — Tue, 11 Aug 2020 22:59:21 GMT

Thank you so much for the reply . I have tried this what if i have 100's of variables and not just three and can also get the count along with the percentage ?

Re: macro to check percentage of missing values in a dataset

chennupriya — Tue, 11 Aug 2020 23:15:52 GMT

Also , i have both character and numeric variables in the Dataset

Re: macro to check percentage of missing values in a dataset

ballardw — Tue, 11 Aug 2020 23:35:46 GMT

You can use variable lists on CLASS, Format and Tables statements.

proc tabulate data =have;

class _numeric_ _character_ /missing;

format _numeric_ nmissfmt _character_ $missfmt. ;

tables _numeric_ _character_ ,

category*(n colpctn)

;

run;

This changes the row/column order from the first because if you have "100's" of variables then across is extremely likely to be very hard to use.

This will create a combination of category with itself.

There are a number of statistics that you can request. That is what the documentaiton is for.

Actually if this had been my data I likely would have created indicator variables with a value of 1 for not missing and then 0 for missing (or vice versa) and then use statistics Sum (total is number of 1's) and Mean(decimal percentage of 1's).

But very few of my data sets have 100's of variables that need checking.

Re: macro to check percentage of missing values in a dataset

chennupriya — Tue, 11 Aug 2020 23:46:42 GMT

Hi ,
I ran above code but i get below error

24 proc tabulate data =test;
25 class _numeric_ _character_ /missing;
26 format _numeric_ nmissfmt _character_ $missfmt. ;
ERROR: You are trying to use the character format $MISSFMT with the numeric variable XX in data set WORK.TEST.
ERROR: You are trying to use the character format $MISSFMT with the numeric variable YY in data set WORK.TEST.
WARNING: Variable NMISSFMT not found in data set WORK.TEST.
27 tables _numeric_ _character_ ,
28 category*(n rowpctn)
29 /
30 ;
31 run;

Re: macro to check percentage of missing values in a dataset

Tom — Wed, 12 Aug 2020 02:52:21 GMT

Because you left the period out of the format specification instead of trying to apply two different formats to two different lists of variables you are trying to apply one format to all of the variable, including one that does not exist.

Re: macro to check percentage of missing values in a dataset

Ksharp — Wed, 12 Aug 2020 12:11:12 GMT

It is easy for PROC SQL.

Make a macro to make the following code streamline .

data have;
set sashelp.heart;
if ranuni(1) < 0.2 then call missing(AgeAtStart,status);
run;

proc sql;
create table want as
select sex,
 nmiss(AgeAtStart)/count(*) as nmiss_per_AgeAtStart,
 nmiss(status)/count(*) as nmiss_per_status,
 nmiss(height)/count(*) as nmiss_per_height
from have
 group by sex;
quit;

Re: macro to check percentage of missing values in a dataset

Ksharp — Wed, 12 Aug 2020 12:15:24 GMT

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2020/4737-2020.pdf

Re: macro to check percentage of missing values in a dataset

chennupriya — Thu, 13 Aug 2020 03:20:50 GMT

Thank you so much for all your help

Re: macro to check percentage of missing values in a dataset

chennupriya — Thu, 13 Aug 2020 03:21:54 GMT

Thank you for all your help