Dear All, I am trying to create a do loop where n will create a new column (withdrawal_&n) as well as act as a multiplier. While the code runs ok, each generated column is overwritten by the next e.g. withdrawals_4 overwrites withdrawal_3. IS there a way around this in proc sql? I appreciate that I can do the same in data step if I refer to the same table, but my preference is to do it within proc sql.
I'm just learning the ropes to sas so any help would be greatly appreciated.
%macro sqlloop;
proc sql;
%do n = 2 %to 4;
%let field = Withdrawals_&n;
create table test as
select *,
SUM(case
WHEN time_on_books in (11,12,13)
THEN BAL_THISMTH END) as Withdrawals_1,
SUM(case
WHEN time_on_books = (12*(&n-1))+11 or time_on_books = (12*(&n-1))+12 or time_on_books = (12*(&n-1))+13
THEN BAL_THISMTH END) as &field,
FROM IS_tb1;
%end;
quit;
%mend;
%sqlloop
It looks like you are trying to get the variables all onto the same dataset, so I would alter your code this way, which will create a single dataset having withdrawals_1-withdrawals_4:
%macro sqlloop;
proc sql;
create table test as
select *,
SUM(case
WHEN time_on_books in (11,12,13)
THEN BAL_THISMTH END) as Withdrawals_1
%do n=2 %to 4;
, SUM(case
WHEN time_on_books = (12*(&n-1))+11 or time_on_books = (12*(&n-1))+12 or time_on_books = (12*(&n-1))+13
THEN BAL_THISMTH END) as withdrawals_&n
%end;
FROM IS_tb1;
quit;
%mend;
%sqlloop
Since you a reusing the name of the created table (test) then the last version is the only one I would expect to see. You might want to try 'create table test&n as' to create a separate output table for each run.
OR have only the code that uses the macro loop variable within the loop. Maybe move the proc sql ,table and select before the %do and only have the case assignment within the loop. It will be up to you to get the comma between results in the right place (i.e. no comma after the last variable created)
Thank you for your response. I am still struggling to get it to work. Is there an alternative way you would recommend doing this in data step?
Regards,
Michael
Hello,
As said you might want to "use the macro loop variable within the loop". The following solution may be what you are looking for:
%macro sqlloop;
proc sql;
create table test as
select *,
SUM(case
WHEN age in (11,12,13)
THEN height end) as Withdrawals_1
%do n = 2 %to 4;
%let field = Withdrawals_&n;
,SUM(case
WHEN age = (6*(&n-1))+5 or age = (6*(&n-1))+6 or age = (6*(&n-1))+7
THEN height end) as &field
%end;
FROM sashelp.class;
quit;
%mend;
%sqlloop
Hi,
Could you supply some test data and required output so that I can see what you are attempting to do as currently it makes no sense.
Loko, I am afraid your code doesn't make sense as all Withdrawals_1 and Withdrawals_2 will be exactly the same, 3-4 will not be populated. And the logic also doesn't make sense as it only looks at Age 11, 12, 13 for 2, 17, 18,19 for 3 and 23, 24, 25 for 4, so missing out all the other data but still summing that data?
Hi Micksom,
Please find below the code that will work for you:
data new;
input emp $ empid;
datalines;
a 1
b 2
c 3
d 4
e 5
;
run;
%macro sqlloop;
proc sql;
create table test as
select *
%do n = 2 %to 10;
%let field = Withdrawals_&n;
empid as &field
%end;
FROM new;
quit;
proc print data=test;
run;
%mend;
%sqlloop;
Here's how the output looks like:
It looks like you are trying to get the variables all onto the same dataset, so I would alter your code this way, which will create a single dataset having withdrawals_1-withdrawals_4:
%macro sqlloop;
proc sql;
create table test as
select *,
SUM(case
WHEN time_on_books in (11,12,13)
THEN BAL_THISMTH END) as Withdrawals_1
%do n=2 %to 4;
, SUM(case
WHEN time_on_books = (12*(&n-1))+11 or time_on_books = (12*(&n-1))+12 or time_on_books = (12*(&n-1))+13
THEN BAL_THISMTH END) as withdrawals_&n
%end;
FROM IS_tb1;
quit;
%mend;
%sqlloop
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.