Hi @andreas_lds , It's very different way of yours 😄 but here what I've tried so far with still some bugs. Apparently creating macro variable inside a macro statement doesn't work outside. ( see my post : https://communities.sas.com/t5/SAS-Programming/Do-Loop-with-Proc-SQL-inside-a-macro/m-p/563657#M158044) Thank you for helping me, I will try to understand your code and adapt it to my needs. %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;
... View more
