topic Re: Trying to do loop on string in SAS Programming

Trying to do loop on string

camfarrell25 — Mon, 06 Feb 2017 15:32:42 GMT

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;

Re: Trying to do loop on string

DanielSantos — Mon, 06 Feb 2017 15:44:19 GMT

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

Re: Trying to do loop on string

camfarrell25 — Mon, 06 Feb 2017 15:46:47 GMT

Yes.... My mistake I oversaw a non-related glitch.

Thank you for confirming I was on the right tracks!