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.
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.
Your macro call can be replaced by
%let terms_id=2021FA;
as it does nothing else.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.