BookmarkSubscribeRSS Feed
elsfy
Quartz | Level 8

Hi, this code below creates 4 variables with the same value for each product_code except for "WWW" which  has a different value in the 4 variables depending on the table it comes from

i want to replace this code with a loop : 


proc sql ; 
create table want as select 
a.* , 
CASE WHEN a.product_code = "XXX"  then b.product
	 WHEN a.product_code = "WWW"  then c1.product
	 WHEN a.product_code = "YYY"  then d.product
end as new_code_1 ,

CASE WHEN a.product_code = "XXX"  then b.product
	 WHEN a.product_code = "WWW"  then c2.product
	 WHEN a.product_code = "YYY"  then d.product
end as new_code_2 , 

CASE WHEN a.product_code = "XXX"  then b.product
	 WHEN a.product_code = "WWW"  then c3.product
	 WHEN a.product_code = "YYY"  then d.product
end as new_code_3 , 

CASE WHEN a.product_code = "XXX"  then b.product
	 WHEN a.product_code = "WWW"  then c4.product
	 WHEN a.product_code = "YYY"  then d.product
end as new_code_4

from table1 a 
left join table2 b on a.id = b.id
left join table3_1 c1 on a.id = c1.id
left join table3_2 c2 on a.id = c2.id
left join table3_3 c3 on a.id = c3.id
left join table3_4 c4 on a.id = c4.id
left join table4 d on a.id = d.id
;
quit; 

So i did this : 

%m();

%do i = 1 %to 4 ;
proc sql ; 
create table want as select 
a.* , 
CASE WHEN a.product_code = "XXX"  then b.product
	 WHEN a.product_code = "WWW"  then c&i..product
	 WHEN a.product_code = "YYY"  then d.product
end as new_code_&i. 

from table1 a 
let join table2 b on a.id = b.id
let join table3_&i. c on a.id = c&i.id
let join table4 d on a.id = d.id

;
quit ;
%end;

%mend;
%m()

 but it creates only one variable coming from the last table3_4 : new_code_4 while i want 4 variables.

 

Thanks

1 REPLY 1
Tom
Super User Tom
Super User

Looks like you have placed the %DO loop in the wrong place.  You need to loop to generate the repetitive part of the code, not the whole code over and over again.

proc sql ; 
create table want as select 
  a.* 
%do i=1 %to 4 ;
, CASE WHEN a.product_code = "XXX"  then b.product
	 WHEN a.product_code = "WWW"  then c&i..product
	 WHEN a.product_code = "YYY"  then d.product
end as new_code_&i. 
%end;
from table1 a 
left join table2 b on a.id = b.id
%do i=1 %do 4;
left join table3_&i. c&i. on a.id = c&i..id
%end;
left join table4 d on a.id = d.id
;
quit; 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 823 views
  • 0 likes
  • 2 in conversation