BookmarkSubscribeRSS Feed
SAS_N00b
Fluorite | Level 6

 

%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.

 

4 REPLIES 4
ballardw
Super User

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.

 

SAS_N00b
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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;

 

Astounding
PROC Star

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

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2432 views
  • 0 likes
  • 4 in conversation