topic Re: Macro to Identify missing values in SAS Programming

Macro to Identify missing values

adityaa9z — Thu, 13 Aug 2020 11:17:09 GMT

Hello everyone,

Has anyone worked on a macro before that helps us identify missing values for different column in a dataset. Hoping to get an intuitive macro which tells how many and row number where values are missing. Example Name Age Sex.

If there are 2 values missing in name, 4 in age and 5 in sex, the macro should be able to tell this info.

Planning to use in my code once the final dataset is ready so I can just run this macro and check for missing fields, values.

Re: Macro to Identify missing values

PaigeMiller — Thu, 13 Aug 2020 11:21:01 GMT

No macro needed here.

See this code from @Watts https://communities.sas.com/t5/SAS-Programming/macro-to-check-percentage-of-missing-values-in-a-dataset/m-p/676018#M203772

Re: Macro to Identify missing values

adityaa9z — Thu, 13 Aug 2020 11:27:25 GMT

Hi. I have to use it on a daily basis for different datasets so adding this code doesn't seem a viable solution which I why was hoping for a macro instead.

Re: Macro to Identify missing values

Ksharp — Thu, 13 Aug 2020 12:08:19 GMT

%macro check_miss(dsn= , out= );
/* firstl, get variable names */
proc transpose data=&dsn(obs=0) out=vname;
var _all_;
run;
/* then check if variable is all missing */
proc sql noprint;
select catx(' ','nmiss(',_name_,') as',_name_) into :vnames separated by ','
from vname;
create table temp as
select &vnames from have;
quit;
proc transpose data=temp out=&out;
var _all_;
run;
%mend;




/*Test Macro*/
data have;
 set sashelp.class;
 if _n_ in ( 1 4 6) then call missing(name,age);
run;
%check_miss(dsn=have, out=want)

Re: Macro to Identify missing values

PaigeMiller — Thu, 13 Aug 2020 12:38:16 GMT

@adityaa9z wrote:
Hi. I have to use it on a daily basis for different datasets so adding this code doesn't seem a viable solution which I why was hoping for a macro instead.

But the code will work on every data set, regardless of variable names. How can code that works on every data set, regardless of variable names, not be viable?

Re: Macro to Identify missing values

bknitch — Thu, 13 Aug 2020 16:52:09 GMT

Check out this article, it may be of some use to you.

https://www.lexjansen.com/nesug/nesug06/cc/cc12.pdf