topic Re: Macro to automate checks on columns in SAS Programming

Macro to automate checks on columns

Sean100 — Wed, 25 Apr 2018 11:08:08 GMT

Hi,

I have been given a massive table (800+ columns, over 200,000 rows) with both character and numeric columns. I have been asked to find the proportion of missing data in each column, both overall and the average annually, and present the results in a table.

I have written macros for both numeric and character variables which will produce a row of the required data and append it to a table which will become the final table.

However, at the moment I will have to write all 800+ variable names into the macros one by one and then run it all at once.

Is there any way to write a macro that will run through the variables, decide if the current variable is numeric or character, and put it into the appropriate macro?

For example, something like:

%macro master_macro(dataset, date_var);

while(current variable exists)

if the current variable is numeric do

my_numeric_macro(dataset, current variable, date_var);

else do

my_character_macro(dataset, current variable, date_var);

current variable = next variable

%mend

Apologies for the sloppy pseudo code!

Re: Macro to automate checks on columns

Astounding — Wed, 25 Apr 2018 11:55:38 GMT

If you search the forum here, you will probably find that this question has been addressed in ways that are shorter ... you don't need to name each variable individually. However, if that doesn't appeal to you, the easiest route might be to divide the variables into separate data sets:

data numerics (keep=_numeric_)

characters (keep=_character_);

set have;

run;

Now you are guaranteed that every variable in one data set is numeric, and every variable in the other data set is character.

Re: Macro to automate checks on columns

RW9 — Wed, 25 Apr 2018 13:28:04 GMT

You do not need macro at all. Your problem can be broken down into two simple steps - get nmiss of number, get nmiss of characters, for instance, produce a report of missing numbers:

proc means data=have nmiss;
  var _numeric_;
run;

There are loads of very simple methodolgies for doing what you want, for instance:

https://stats.idre.ucla.edu/sas/faq/how-can-i-see-the-number-of-missing-values-and-patterns-of-missing-values-in-a-data-file/

You can use means, summary, freq, datastep, sql, etc.