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
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;
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!
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.
Ready to level-up your skills? Choose your own adventure.