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