topic Re: Question about a SAS macro to calculate missing data percentage of each variable in the data set in SAS Data Management

Question about a SAS macro to calculate missing data percentage of each variable in the data set

Jade_SAS — Fri, 02 Nov 2018 13:17:48 GMT

Hi All,

I found a macro in the forum to calculate the missing data percentage for each variable in the data set. I tried it and it works, but for some reason the last variable in the data set will be get rid of, I wonder how to fix this macro. Is there anybody can help? Thank you!

1) Macro

%macro missing_data(library=,dataset=,xoutdataset=);

proc sql noprint;

select name into :allvars separated by " " from dictionary.columns

where libname="%upcase(&library.)" and memname="%upcase(&dataset.)";

quit;

proc sql noprint;

create table &xoutdataset. as

select

%let firstspace=%sysfunc(find(&allvars.,%str( )));

%do %while (&firstspace.>0);

%let var=%substr(&allvars.,1,&firstspace.);

sum(missing(&var.))/count(*) as &var.,

%let allvars=%substr(&allvars.,&firstspace.);

%let firstspace=%sysfunc(find(&allvars.,%str( )));

%end;

'Y' as done_successfully

from &library..&dataset.;

quit;

%mend missing_data;

%missing_data(library=sashelp,dataset=class,xoutdataset=outfile);

2) The output variable list after macro used:

Name Sex Age Height done_successfully

3) The variable list in the data set:

Name Sex Age Height Weight

Compare 2) and 3), we can see the weight variable is gone after using the macro. I wonder how this macro be fixed to include every variables in the data set.

Thanks,

Jade

Re: Question about a SAS macro to calculate missing data percentage of each variable in the data set

PaigeMiller — Fri, 02 Nov 2018 13:28:58 GMT

Something in the looping over the variables gets screwed up by this code. I will leave it to others to figure out exactly where the error is (if understanding the exact error is of interest). However, the code below doesn't have this problem

%macro missing_data(library=,dataset=,xoutdataset=);
    proc sql noprint;
        select name into :allvars separated by " " from dictionary.columns
        where libname="%upcase(&library.)" and 
        memname="%upcase(&dataset.)";
    quit;
    proc sql noprint;
        create table &xoutdataset. as select
        %do i=1 %to %sysfunc(countw(&allvars));
              %let var=%scan(&allvars,&i,%str( ));
              sum(missing(&var.))/count(*) as &var. 
              %if &i<%sysfunc(countw(&allvars)) %then , ;
        %end;
        from &library..&dataset.;
    quit;
%mend missing_data;

Re: Question about a SAS macro to calculate missing data percentage of each variable in the data set

Astounding — Fri, 02 Nov 2018 13:35:23 GMT

There is no blank space at the end of &ALLVARS, so the FIND function can't locate one to determine when the last variable name ends.

Consider modifying here:

%let allvars=%substr(&allvars.,&firstspace.+1);

%let firstspace=%sysfunc(find(%str(&allvars. ),%str( )));

The first change removes the leading blank from &ALLVARS. Then the second change adds a blank to the end of &ALLVARS. (Not sure that the first change is required, but you can test it and report back to the group.)

Re: Question about a SAS macro to calculate missing data percentage of each variable in the data set

Jade_SAS — Fri, 02 Nov 2018 13:38:14 GMT

Thank you Paige! Your macro worked!

Re: Question about a SAS macro to calculate missing data percentage of each variable in the data set

Ksharp — Fri, 02 Nov 2018 13:39:21 GMT

Try this code.

data have;
 set sashelp.class;
 call missing(age ,sex);
run;

/*firstly get variable name*/
proc transpose data=have(obs=0) out=vname;
var _all_;
run;
/*then know  missing percent*/
proc sql noprint;
select catx(' ','nmiss(',_name_,')/count(*) as',_name_) into :vnames separated by ','
from vname;
create table want as
select &vnames from have;
quit;

Re: Question about a SAS macro to calculate missing data percentage of each variable in the data set

Kurt_Bremser — Fri, 02 Nov 2018 13:44:37 GMT

When you use %substr, the resulting macro variable has no blanks for padding (as opposed to what you have in a data step), so the resulting value for firstchar is zero when there's one item left.

The way the loop is built, someone out-thunk him/herself when working around the non-existent %countw() function.

The proper method for such loops has been posted by @PaigeMiller: use %sysfunc(countw()) and a simple iterative do loop.

And support my suggestion for the introduction of a %countw macro function:

https://communities.sas.com/t5/SASware-Ballot-Ideas/Macro-equivalent-for-countw/idi-p/409066