Do Loop with Proc SQL inside a macro

[adil256]

Hi everyone,

I tried to run a do loop for a Proc SQL in a macro but my procedure fails.

When I run separately the  %Do_Stock macro, there is no warning from SAS. However, the macro-variable Expo_d_&i. and Expo_m_&i. doesn't seem to work.

If you have any idea on how to fix the problem I would very happy.

Thank you to the community.

data Have;
length ID Status $ 40;
input Id  Status $ Date_of_effect Date_Status ;

informat Date_of_effect Date_Status date9.;
format Date_of_effect Date_Status date9.;
infile datalines missover;
datalines;
1 Ongoing 05jul2015 05jul2015
1 Transformation 23Oct2014 05jul2015 
2 Death 07jan2017 02dec2018 
3 Finished 13feb2005 13feb2015
4 NoEffect 06nov2016 06nov2016
5 Cancellation 08MAR2014 19dec2018
6 Pending 21oct2018 21oct2018
;

run;

%let a=2015;
%let b=2019;


data Want_0 (drop=i) ;
set have;

array Expo_d_ (&a.:&b.) Expo_d_&a.-Expo_d_&b.;
array Expo_m_ (&a.:&b.) Expo_m_&a.-Expo_m_&b.;
if Status = "Ongoing" or Status="Pending" then
Date_Status=today();

do i=&a. to &b.;

Expo_d_(i) = max(0,min(mdy(01,01,i+1),Date_Status)-max(Date_of_effect,mdy(1,1,i)));
Expo_m_(i) = max(0,round((min(mdy(01,01,i+1),Date_Status)-max(Date_of_effect,mdy(1,1,i)))/(365.25/12),1));
end;
run;



%macro Do_stock;

proc sql noprint;

%do i=&a. %to &b.;
select sum(Expo_d_&i.) into: Expo_d_&i.
from work.Want_0;

select sum(Expo_m_&i.) into: Expo_m_&i.
from work.Want_0;
%end;                                                       

quit;
run;

%do i=&a. %to &b.;

%put Expo_d_&i.;
%put Expo_m_&i.;

%end;
%mend;
%Do_stock;

data Want_1 ( drop=i);
do i = &a. to &b.;

year= i;

Expo_day=&Expo_d_&i.;
Expo_month=&Expo_m_&i.;
end;

run;

[Kurt_Bremser]

Macro variables created within a macro (and not already present "outside", in the global symbol table) are created in the local symbol table of the macro and vanish as soon as the macro stops executing. To have them available outside, you need to name them in a %global statement before you create them.

And your final data step should probably look like this:

data want_1;
do year = &a. to &b.;
  Expo_day = symget(cats("Expo_d_",year));
  Expo_month = symget(cats("Expo_m_",year));
  output;
end;
run;

But this whole code looks like a serious attempt at code obfuscation to me; I guess that the whole operation could be achieved without any macro programming at all. Every time I see people putting data values in macro variable lists, there's a serious misunderstanding of the macro preprocessor and a serious design mistake at work.

Please describe your issue in plain language.