Do loop for creating new variables in SAS

Question

I am trying to run this code

data swati;
input facility_id$ loan_desc : $50. sys_name :$50.;
cards;
fac_001 term_loan     RM_platform
fac_001 business_loan IQ_platform
fac_002 business_loan BUSES_termloan
fac_002 business_loan RM_platform
fac_003 overdrafts    RM_platform
fac_003 RCF           IQ_platform
fac_003 term_loan     BUSES_termloan
;

proc contents data=swati out=contents(keep=name varnum);
run;

proc sort data=contents;
by varnum;
run;

data contents;
set contents ;
where varnum in (2,3);
run;

data contents;
set contents;
summary=catx('_',name, 'summ');
run;

data _null_;
set contents;
call symput ("name" || put(_n_ , 10. -L), name);
call symput ("summ" || put (_n_ , 10. -L), summary);
run;

options mlogic symbolgen mprint;
%macro swati;

%do i = 1 %to 2;
proc sort data=swati;
by facility_id &&name&i.;
run;

data swati1;
set swati;
by facility_id &&name&i.;
length &&summ&i. $50.;
retain &&summ&i.;
if first.facility_id then do;
&&summ&i.="";
end;
if first.&&name&i. = last.&&name&i. then &&summ&i.=catx(',',&&name&i., &&summ&i.);
else if first.&&name&i.  ne last.&&name&i. then &&summ&i.=&&name&i.;
run;

if last.facility_id ;
%end;
%mend;
%swati;

This code will create two new variables loan_desc_summ and sys_name_summ which has values of the all the loans_desc in one line and the sys_names in one line seprated by comma example (term_loan, business_loan), (RM_platform, IQ_platform) But if a customer has only one loan_desc the loan_summ should only have its value twice.

The problem while running the do loop is that after running this code, I am getting the dataset with only the sys_name_summ and not the loan_desc_summ. I want the dataset with all the five variables facility_id, loan_desc, sys_name, loan_desc_summ, sys_name_summ.

Could you please help me in finding out if there is a problem in the do loop??

Answer 1

Your loop is always starting with the same input dataset (swati) and generating a new dataset (SWATI1). So only the last time through the loop has any effect. Each loop would need to start with the output of the previous run. You also need to fix your logic for eliminating the duplicates.

For example you could change the macro to:

%macro swati;
data swati1;
  set swati;
run;

%do i = 1 %to 2;
  proc sort data=swati1;
    by facility_id &&name&i.;
  run;
  data swati1;
    set swati1;
    by facility_id &&name&i ;
    length &&summ&i $500 ;
    if first.facility_id then &&summ&i = ' ' ;
    if first.&&name&i then catx(',',&&summ&i,&&name&i);
    if last.facility_id ;
  run;
%end;
%mend;

Also your program could be a lot smaller if you just used arrays.

data want ;
  set have ;
  by facility_id ;
  array one loan_desc sys_name ;
  array two $500 loan_desc_summ sys_name_summ ;
  retain loan_desc_summ sys_name_summ ;
  do i=1 to dim(one);
    if first.facility_id then two(i)=one(i) ;
    else if not findw(two(i),one(i),',','t') then two(i)=catx(',',two(i),one(i));
  end;
  if last.facility_id;
  drop i loan_desc sys_name ;
run;

If you want to make it more flexible you can put the list of variable names into a macro variable.

%let varlist=loan_desc sys_name;

You could then generate the list of new names easily.

%let varlist2=%sysfunc(tranwrd(&varlist,%str( ),_summ%str( )))_summ ;

Then you can use the macro variables in the ARRAY, RETAIN and DROP statements.