topic Re: Macro Loop for Merge in SAS Programming

Macro Loop for Merge

Toni2 — Wed, 27 Apr 2022 14:21:59 GMT

i edited since i might have caused some confusion.

Below is my full code which i use to create PSI index for a number of variables (var1 var2 var3....)

I want to create the below summary table

Var_Name	PSI
Var1	value
Var2	value
…	value
Var100	value

and i want to be part of the existing macro

my understanding is that i need to use this line of the code :

data modeling_oot_&i;

*** Part I: Define Data Set and Variables ****;

%let inputset= dataset1; /* The modeling sample */
%let compareset= dataset2; /* The validation sample */
%let varnum=var1 var2 var3 var4;/* list of numeric variables */
%let binnum=10; /* number of bins for numeric variables */
%let vartxt=; /* list of character variables */
%let imtxt=____; /* label for missing character values */
%let missnum=-999999999; /* label for missing character values */
%let yaxislabel=% Frequency; /* label for distribution */
%let labelmod=Population; /* label for modeling sample */
%let labeloot=Sample; /* label for validation sample */


********* Part II: Numeric Variables *********;

%macro dealnum;

%if %sysfunc(countw(&varnum dummyfill)) > 0 %then %do;

data check_contents;
retain &varnum;
set &inputset(keep=&varnum obs=1);
run;

proc contents data=check_contents varnum out=check_contents2 noprint;
run;

proc sort data=check_contents2(keep=name varnum) out=checkfreq(rename=(name=tablevar)); 
by varnum; 
run;

data varcnt; 
set checkfreq; 
varcnt+1;
run;

proc sql noprint;
select tablevar into :varmore separated by ' ' from varcnt;
quit;

proc sql;
create table vcnt as select count(*) as vcnt from varcnt;
quit;

data _null_; 
set vcnt;
call symputx('vmcnt', vcnt);
run;

proc sql noprint; 
select tablevar into :v1-:v&vmcnt from varcnt;
quit;

proc rank data=&inputset group=&binnum out=check_rank ties=low;
var &varnum;
ranks rank1-rank&vmcnt; 
run;

data check_rank;
set check_rank;
array fillmiss(*) rank1-rank&vmcnt;
do j=1 to dim(fillmiss);
if fillmiss(j)=. then fillmiss(j)=-1;
fillmiss(j)=fillmiss(j)+1;
end;
drop j;
run;

%macro meannum;
%do i=1 %to &vmcnt;

proc means data=check_rank nway min max median noprint;
class rank&i;
var &&v&i;
output out=check&i(drop=_type_ rename=(_freq_=freq_&i))
min=min_v&i
max=max_v&i
median=&&v&i;
run;

data check&i; 
set check&i; 
rank_num_&i+1; 
run;

proc sql noprint; 
select max(rank_num_&i) into :maxrank from check&i;
quit;

data check&i;
length sas_code $ 256.;
set check&i;
if rank_num_&i=1 then sas_code="if &&v&i le "||max_v&i||" then rank_num_&i=1;";
else sas_code="else if &&v&i le "||max_v&i||" then rank_num_&i="||rank_num_&i||";";
if rank_num_&i=&maxrank then sas_code="else rank_num_&i="||rank_num_&i||";";
sas_code=compbl(sas_code);
run;

proc sort data=check&i;
by rank_num_&i;
run;

proc sql noprint;
select sas_code into :algnum&i separated by ' ' from check&i;
quit;

data check_mod_sample;
set check_rank;
&&algnum&i;
run;

data check_oot_sample; 
set &compareset; 
&&algnum&i;
run;

proc freq data=check_mod_sample noprint;
tables rank_num_&i/out=modeling_freq(rename=(count=count_mod percent=freq_mod));
run;

proc freq data=check_oot_sample noprint;
tables rank_num_&i/out=oot_freq(rename=(count=count_oot percent=freq_oot));
run;

proc sort data=modeling_freq;
by rank_num_&i;
run;

proc sort data=oot_freq;
by rank_num_&i;
run;

proc sort data=check&i;
by rank_num_&i;
run;

proc sql noprint;
select count(*) into :totcntoot from check_oot_sample;
quit;

proc sql noprint;
select sum(count_mod) into :totcntmod from modeling_freq;
quit;

proc sql noprint; 
select sum(count_oot) into :totcntoot from oot_freq;
quit;

proc sql noprint;
select sum(freq_mod) into :totfreqmod from modeling_freq;
quit;

proc sql noprint;
select sum(freq_oot) into :totfreqoot from oot_freq;
quit;

data modeling_oot_freq;
merge modeling_freq oot_freq check&i(keep=rank_num_&i &&v&i sas_code);
by rank_num_&i;
if count_oot=. then count_oot=0;
if freq_oot=. then freq_oot=1/&totcntoot;
freq_mod=freq_mod/100;
freq_oot=freq_oot/100;
if freq_mod > freq_oot then PSI=(freq_oot-freq_mod)*log(freq_oot/freq_mod);
else PSI=(freq_mod-freq_oot)*log(freq_mod/freq_oot);
order_rank=put(rank_num_&i, 32.);
run;

proc sql noprint; select sum(PSI) into :psi from modeling_oot_freq; quit;
data for_total;
order_rank="Total";
PSI=&psi;
count_mod=&totcntmod;
count_oot=&totcntoot;
freq_mod=&totfreqmod/100;
freq_oot=&totfreqoot/100;
run;

data modeling_oot_&i; 
set modeling_oot_freq for_total;
keep order_rank PSI;
where order_rank="Total";
order_rank="&&v&i";
format freq_mod 6.4;
format freq_oot 6.4;
informat freq_mod 6.4;
format freq_oot 6.4;
run;



proc print data=modeling_oot_&i /*(drop=sas_code)*/ noobs; /*title "&&v&i"; */;
run;

data modeling_oot_for_graph;
set modeling_oot_&i;
if compress(order_rank)='1' and &&v&i=. then &&v&i=&missnum;
run;

%end;
%mend meannum;
%meannum;
%end;
%mend dealnum;
%dealnum;

Re: Macro Loop for Merge

PaigeMiller — Wed, 27 Apr 2022 13:28:20 GMT

No macro needed

data want;
    set have_1 have_2 have_3 have_4;
run;

By the way, this is not a "merge".

Re: Macro Loop for Merge

Ksharp — Wed, 27 Apr 2022 13:32:32 GMT

Maybe you want this ?

%macro append(dsn=);
proc append base=want1 data=&dsn. force;run;
%mend;

proc delete data=want1;run;
%append(dsn=want2)
%append(dsn=want3)
%append(dsn=want4)

Re: Macro Loop for Merge

Toni2 — Wed, 27 Apr 2022 14:22:34 GMT

i have edited it my comment since it would be difficult to explain

Re: Macro Loop for Merge

Toni2 — Wed, 27 Apr 2022 14:25:53 GMT

thanks it works but it causes "warning". I have edited my initial comment in order which will make it easier

Re: Macro Loop for Merge

PaigeMiller — Wed, 27 Apr 2022 14:29:34 GMT

@Toni2 wrote:
i have edited it my comment since it would be difficult to explain

This is dramatically different than your original question ... it will take me some time to look through this and come up with an answer.

Re: Macro Loop for Merge

Toni2 — Wed, 27 Apr 2022 14:30:23 GMT

yes, i know, i am sorry...thanks for the support

Re: Macro Loop for Merge

Tom — Wed, 27 Apr 2022 14:47:07 GMT

So your edit has moved the post from too little information to too much. Now it is is not clear what part of that long program you are having issues with.

I suspect that you want to add either a PROC APPEND step (if the datasets have consistent structure) or a DATA step (if they don't) somewhere after you have created modeling_oot_&i dataset and before the %END for the %DO I= loop.

proc append base=model_oot_all data=modeling_oot_&i;
run;

data model_oot_all;
   set 
%if &I > 1 %then model_oot_all ;
       modeling_oot_&i
  ;
run;

PS Your program has the macro definitions interwoven with the execution. To save your sanity and make it possible to edit the code move the macro definitions to the top. Do not nest macro definitions inside each other. You can see the confusion that causes by just looking at these lines near the bottom of your code.

%end;
%mend meannum;
%meannum;
%end;
%mend dealnum;
%dealnum;

If you have two macros named %DEALNUM and %MEANNUM then the program flow should be:

%macro meannum;
...
%mend meannum;
%macro dealnum;
...
  %meanum;
...
%mend dealnum;

* Start of actual executable code ;
* setup SAS code to make input data ;
%dealnum;
*cleanup SAS code ;

Re: Macro Loop for Merge

Toni2 — Wed, 27 Apr 2022 14:59:36 GMT

Great, it works! your understanding is correct. Just one point, when i run it multiple times it replicates the results - see below

PSI	order_rank
0.008232	Var1
0.003461	Var2
0.006708	Var3
0.000037	Var4
0.008232	Var1
0.003461	Var2
0.006708	Var3
0.000037	Var4

Re: Macro Loop for Merge

Tom — Wed, 27 Apr 2022 15:09:15 GMT

Part of the setup is to make sure the dataset you are using to aggregate into does not exist.

Delete or rename the output dataset before running again. Something like:

proc delete data=output_all;
run;

Why did you run it more than once? Are you debugging the logic?

Or are both runs important?

Perhaps there is some other macro parameter value that should be use in naming the output dataset?
Or added as a value in an additional variable in the output dataset so you can distinguish which runs the observations are from.

Re: Macro Loop for Merge

Toni2 — Wed, 27 Apr 2022 15:36:36 GMT

thanks, yes, it works but it still produces replicates. Possibly, i don't place it in the right location.

I run the code multiple times as i make changes.

I want every time which i run it to get the correct results - if it is possible?

Yes, we can adjust the name of the output

any idea how we can adjust the code?