DATA Step, Macro, Functions and more

Trying to do loop on string

Accepted Solution Solved
Reply
Contributor
Posts: 27
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

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

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: 27

Re: Trying to do loop on string

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
  • 124 views
  • 1 like
  • 2 in conversation