Hello,
I'm trying to do a loop that assigns the value &year from 2010 to 2016 to the code below, but it seems I'm constantly getting stuck at getting the case t8.report = '&year.0331' to work... if i write it out '20100301' it works but it seems my macro variable won't pick it up.
THanks in advance.
%macro loop;
%do year=2010 %to 2016;
proc sql;
create table historicalmatch&year as select unique t1.ORIACCTN,
case when t8.report = "&year.0331" then t8.reporter end as Q1&year,
case when t8.report = "&year.0630" then t8.reporter end as Q2&year,
case when t8.report = "&year.0930" then t8.reporter end as Q3&year,
case when t8.report = "&year.1231" then t8.reporter end as Q4&year,
case when t8.lnacctno is missing then 0 else 1 end as Match&year
from out.APPROVED_AVLENDCD_CONDENSED t1
left join iif.iif&year t8 on (input(t1.ORIACCTN,8.) = t8.lnacctno);
quit;
%end;
%mend;
%loop;
Hi.
Not sure of what's happening there, but here it cycles quite perfectly.
create table historicalmatch2010 as select unique t1.ORIACCTN, case when t8.report = "20100331" then
t8.reporter end as Q12010, case when t8.report = "20100630" then t8.reporter end as Q22010, case when
t8.report = "20100930" then t8.reporter end as Q32010, case when t8.report = "20101231" then
t8.reporter end as Q42010, case when t8.lnacctno is missing then 0 else 1 end as Match2010 from
out.APPROVED_AVLENDCD_CONDENSED t1 left join iif.iif2010 t8 on (input(t1.ORIACCTN,8.) = t8.lnacctno)
...
create table historicalmatch2016 as select unique t1.ORIACCTN, case when t8.report = "20160331" then
t8.reporter end as Q12016, case when t8.report = "20160630" then t8.reporter end as Q22016, case when
t8.report = "20160930" then t8.reporter end as Q32016, case when t8.report = "20161231" then
t8.reporter end as Q42016, case when t8.lnacctno is missing then 0 else 1 end as Match2016 from
out.APPROVED_AVLENDCD_CONDENSED t1 left join iif.iif2016 t8 on (input(t1.ORIACCTN,8.) = t8.lnacctno)
Daniel Santos @ www.cgd.pt
Hi.
Not sure of what's happening there, but here it cycles quite perfectly.
create table historicalmatch2010 as select unique t1.ORIACCTN, case when t8.report = "20100331" then
t8.reporter end as Q12010, case when t8.report = "20100630" then t8.reporter end as Q22010, case when
t8.report = "20100930" then t8.reporter end as Q32010, case when t8.report = "20101231" then
t8.reporter end as Q42010, case when t8.lnacctno is missing then 0 else 1 end as Match2010 from
out.APPROVED_AVLENDCD_CONDENSED t1 left join iif.iif2010 t8 on (input(t1.ORIACCTN,8.) = t8.lnacctno)
...
create table historicalmatch2016 as select unique t1.ORIACCTN, case when t8.report = "20160331" then
t8.reporter end as Q12016, case when t8.report = "20160630" then t8.reporter end as Q22016, case when
t8.report = "20160930" then t8.reporter end as Q32016, case when t8.report = "20161231" then
t8.reporter end as Q42016, case when t8.lnacctno is missing then 0 else 1 end as Match2016 from
out.APPROVED_AVLENDCD_CONDENSED t1 left join iif.iif2016 t8 on (input(t1.ORIACCTN,8.) = t8.lnacctno)
Daniel Santos @ www.cgd.pt
Yes.... My mistake I oversaw a non-related glitch.
Thank you for confirming I was on the right tracks!
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.