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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.