create multiple variables with do loop in proc sql

elsfy — Thu, 03 Nov 2022 14:18:23 GMT

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

Re: create multiple variables with do loop in proc sql

Tom — Thu, 03 Nov 2022 14:35:27 GMT

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;

topic create multiple variables with do loop in proc sql in SAS Programming

create multiple variables with do loop in proc sql

Re: create multiple variables with do loop in proc sql