DATA Step, Macro, Functions and more

Trying to do loop on string

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Trying to do loop on string

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; 

Accepted Solutions
Solution
‎02-06-2017 10:46 AM
Super Contributor
Posts: 474

Re: Trying to do loop on string

Posted in reply to camfarrell25

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

View solution in original post


All Replies
Solution
‎02-06-2017 10:46 AM
Super Contributor
Posts: 474

Re: Trying to do loop on string

Posted in reply to camfarrell25

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

Contributor
Posts: 39

Re: Trying to do loop on string

Posted in reply to DanielSantos

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

Thank you for confirming I was on the right tracks!

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 134 views
  • 1 like
  • 2 in conversation