Hello
Lets say that I want to run statistics on multiple variables.
Lets say that this code is working 100% and I get desired results.
My question-
Is there better way to write the code (using proc step instead of proc sql)?
Please note that in real word I run it on 300 fields and data set has 2 million rows
%macro dist(VAR,tbl,level,format);
%do j=1 %to 6;
%let version=%scan(&Vector_Versions.,&j.,+);
proc sql;
create table _dist_ as
select Left("&VAR.") as Var_name LENGTH=300,
put(&VAR.,&Format.) as Cat LENGTH=100,
count(*) as nr,
calculated nr/(select count(*) as total_nr from &tbl.) as PCT format=percent8.5
from &tbl.
group by calculated CAT
union all
select Left("&VAR.") as Var_name LENGTH=300,
'Total' as CAT,
count(*) as nr,
1 as PCT format=percent8.5
from &tbl.
;
quit;
proc sort data=_dist_;
by var_name level cat_order;
Run;
proc append data=_dist_ base=r_r.Accum_tbl force;quit;
%end;
%mend dist;
%dist(Var=Wealth,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.)
%dist(Var=LoansBalance ,tbl=Y_ttt&version.,level=Y,Format=Fmt_continuous.)
%dist(Var=AVG_PAYOFF_LAST_MON,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.)
%dist(Var=HIYUV_EXLISCAR_AMT,tbl=L_ttt&version.,level=L,Format=Fmt_1F.)
%macro dist(VAR,tbl,level,format);
%do j=1 %to 6;
%let version=%scan(&Vector_Versions.,&j.,+);
proc sql;
create table _dist_ as
select Left("&VAR.") as Var_name LENGTH=300,
put(&VAR.,&Format.) as Cat LENGTH=100,
count(*) as nr,
calculated nr/(select count(*) as total_nr from &tbl.) as PCT format=percent8.5
from &tbl.
group by calculated CAT
union all
select Left("&VAR.") as Var_name LENGTH=300,
'Total' as CAT,
count(*) as nr,
1 as PCT format=percent8.5
from &tbl.
;
quit;
proc sort data=_dist_;
by var_name level cat_order;
Run;
proc append data=_dist_ base=r_r.Accum_tbl force;quit;
%end;
%mend dist;
%dist(Var=Wealth,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.)
%dist(Var=LoansBalance ,tbl=Y_ttt&version.,level=Y,Format=Fmt_continuous.)
%dist(Var=AVG_PAYOFF_LAST_MON,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.)
%dist(Var=HIYUV_EXLISCAR_AMT,tbl=L_ttt&version.,level=L,Format=Fmt_1F.)
... View more
