BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
micksom
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
AmySwinford
Calcite | Level 5

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

View solution in original post

6 REPLIES 6
ballardw
Super User

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)

micksom
Calcite | Level 5

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

Loko
Barite | Level 11

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

damanaulakh88
Obsidian | Level 7


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:

Capture.JPG

AmySwinford
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 11366 views
  • 6 likes
  • 6 in conversation