You have declared the macro variable Term_id to be a %local variable in the macro Do_all. So as soon as the macro portion of the code quits running that macro variable no longer exists. So it cannot be referenced outside of the macro.
I have to say that your "Do_all" is a good candidate for most useless macro of the week. It really performs no task and if you had declared the macro variable Terms_id to be global I still don't see much use for it unless the purpose is to get the last item of a list in an inefficient way.
Here's an example with Term_id actually %global:
%macro do_all(termlist);
%local i;
%global Terms_id;
%do i=1 %to %sysfunc(countw(&termlist,%str( ) ));
%let TERMS_ID=%scan(&termlist,&i,%str( ));
%end;
%mend do_all;
%do_all(2017SU 2017FA 2018SP 2018SU 2018FA 2019SP 2019SU 2019FA
2020SP 2020SU 2020FA 2021SP 2021SU 2021FA)
%put The value of Terms_id is: &terms_id.;
And if you put that Proc SQL code inside the loop from Do_all you would still only create one data set, One_term, overwriting multiple times until it contains the values from the last item in that list.
@cmshearon8845 wrote:
Hi!
This is the first time I'm trying a global macro in a code. I thought I referenced the macro variables in the code, but I'm not sure. I'm not sure what I'm doing wrong. Here is my macro statement:
options symbolgen;
%macro do_all(termlist);
%local i TERMS_ID;
%do i=1 %to %sysfunc(countw(&termlist,%str( ) ));
%let TERMS_ID=%scan(&termlist,&i,%str( ));
%end;
%mend do_all;
%do_all(2017SU 2017FA 2018SP 2018SU 2018FA 2019SP 2019SU 2019FA
2020SP 2020SU 2020FA 2021SP 2021SU 2021FA)
Here is the part of the code with the macro variable:
proc sql;
create table OneTerm as
select drv.TERMS_ID,drv.day,(drv.day+drv.start_dt) as date format
yymmdd10.,drv.Section_Name,
case
when a.CreditHours is missing then 0
else a.CreditHours
end
as CreditHours,
case
when a.SeatCount is missing then 0
else a.Seatcount
end
as Seatcount
from(select distinct a.Terms_ID,a.day,(a.date-a.day) as start_dt format
yymmdd10.,b.Section_Name
from work.AddRegDay as a
left join
(select distinct TERMS_ID, Section_Name from work.AddRegDay where TERMS_ID = "&terms_id"
and Section_Name is not null) b
on a.TERMS_ID=b.TERMS_ID
where a.TERMS_ID = "&terms_id") as drv
left join
(select * from work.AddRegDay where Terms_ID = "&terms_id") as a
on drv.day=a.day and drv.Section_Name=a.Section_Name;
quit;
I'm also attaching the logs from Symbolgen options.
