Desktop productivity for business analysts and programmers

Running all numeric variables through a macro

Reply
Occasional Contributor
Posts: 5

Running all numeric variables through a macro

 

%macro numstats(var = ,file=, format=);
data _null_;
z = find("&var.",'date');
call symput('myeval',z);
run;
%if &myeval. > 0 Or &var. = lpi %then %do;
Proc SQL;
create table &var as 
select "&var." as Varname Length = 35, min(a.&var.) as Minimum FORMAT=&format., max(a.&var.) as Maximum FORMAT=&format., 
ROUND(avg(a.&var.),.01) as Mean, count(*) as Total_Count,x.Count_Null, y.Count_Zero
from &file. a
left join (select "&var." as Var, count(*) as Count_Null from &file.
where &var = .) x
on "&var." = x.var
left join (select "&var." as Var, count(&var.) as Count_Zero from &file.
where &var = 0) y
on "&var." = y.var
where &var. > 0
;
quit;
Data &var (rename=(MyNewVar = Minimum MyNewVar2 = Maximum));
Set &var;
MyNewVar = Put(Minimum,Date9.);
MyNewVar2 = Put(Maximum,Date9.);
*if MyNewVar < 0 then MyNewVar = "";
*if MyNewVar2 < 0 then MyNewVar2 = "";
drop Minimum Maximum;
*format myNewVar yymmddn8. myNewVar2 yymmddn8.;
run;
%end;
%else %do;
Proc SQL;
create table &var as
select "&var." as Varname Length = 35, min(a.&var.) as Minimum, max(a.&var.) as Maximum, 
ROUND(avg(a.&var.),.01) as Mean, count(*) as Total_Count,x.Count_Null, y.Count_Zero
from &file. a
left join (select "&var." as Var, count(*) as Count_Null from &file.
where &var = .) x
on "&var." = x.var
left join (select "&var." as Var, count(&var.) as Count_Zero from &file.
where &var = 0) y
on "&var." = y.var
where &var. > 0
;
quit;
Data &var (rename=(MyNewVar = Minimum MyNewVar2 = Maximum));
Set &var;
MyNewVar = Put(Minimum,$12.);
MyNewVar2 = Put(Maximum,$12.);
if MyNewVar < 0 then MyNewVar = "";
if MyNewVar2 < 0 then MyNewVar2 = "";
drop Minimum Maximum;
run;
%end;
%mend numstats;

 


I have a macro set up that goes through a predefined variable (var) and checks to see whether it is numeric or date-numeric and then formats the statistics generated with the SQL code accordingly.

 

The code is working correctly. My issue is that the numeric variables need to be called out one by one. For example,
%numstats(var = originalprincipalbalance,file=work.filename);.

 

The file has many numeric columns and the columns will change from time to time. Is there a way to setup SAS for it to find all the numeric variables and run them through this macro without me having to call them out one by one?

 

I am hoping this would significantly cut down on someone having to error check all the variables in each file and make sure they are all being run on their own line of code. Also, I would have to have seventy+ lines of code that are essentially the same thing with the variable being the only thing that changes each time.

 

Please help! Thank you in advance.

 

Grand Advisor
Posts: 10,210

Re: Running all numeric variables through a macro

I'm not going to try to figure out what the posted code does. Generally if I want to do something with variable information for multiple variables i start by looking in Dictionary.Columns. You can find the properties of all of the variables in a dataset.

This will send a description to the Log:

proc sql;
   describe table dictionary.columns;
quit;

If I want to look at all the numeric variables in a set:

proc sql;
   create table numerics as
   select * 
   from dictionary.columns
   where type = 'num' and libname='LIBRARY' and
   memname='DATASET';
quit;

Libname and memname in the dictionary table are uppercase.

Then use that info.

 

Occasional Contributor
Posts: 5

Re: Running all numeric variables through a macro

Thank you, ballardw. Now that the table 'Numerics' has been created with the variable names listed under variable 'name.' How can I tell SAS to grab each variable name in this data set and run it through my macro.

 

You do not need to worry about all the code I posted. All it is doing is looking at variables I have to write out and running them through code. I want SAS to recognize the variables that need to be run (all variables in table 'Numerics') and substitute them for the macvar in the macro.

 

Does that make sense?

 

Example:

It  would run %NumericStats (var=Numerics.Name1, file=filename) then when complete would run 

%NumericStats (var=Numerics.Name2, file=filename), then %NumericStats (var=Numerics.Name3, file=filename), until all variables have cycled through the macro.

Super User
Super User
Posts: 6,318

Re: Running all numeric variables through a macro

If you have a list of values in a dataset that you want to use a parameters to a macro it is simple to do that.  So in your case if you have a dataset name MYLIST with the variables VAR and FILE you could run this data step to generate a macro call for each observation.

 

data _null_;
  set mylist ;
  call execute(cats('%nrstr(%NumericStats)'
  ,'(var=', VAR
  ,',file=' FILE
  ,')'
  ));
run;

 

Respected Advisor
Posts: 4,962

Re: SAS EG won't recognize a variable that has the name "Label"

A workaround:  You should be able to rename a variable on the fly.  For example:

 

ods output summary=work.stackodsmeans (rename=(label=varlabel));

Ask a Question
Discussion stats
  • 4 replies
  • 611 views
  • 0 likes
  • 4 in conversation