DATA Step, Macro, Functions and more

Do loop to create new columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Do loop to create new columns

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


Accepted Solutions
Solution
‎09-17-2014 08:37 AM
New Contributor
Posts: 2

Re: Do loop to create new columns

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


All Replies
Super User
Posts: 11,343

Re: Do loop to create new columns

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)

Occasional Contributor
Posts: 9

Re: Do loop to create new columns

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

Super Contributor
Posts: 308

Re: Do loop to create new columns

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

Super User
Super User
Posts: 7,997

Re: Do loop to create new columns

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?

Frequent Contributor
Posts: 81

Re: Do loop to create new columns


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

Solution
‎09-17-2014 08:37 AM
New Contributor
Posts: 2

Re: Do loop to create new columns

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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