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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.