topic Re: Do loop to create new columns in SAS Programming

Do loop to create new columns

micksom — Mon, 15 Sep 2014 22:17:12 GMT

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

Re: Do loop to create new columns

ballardw — Mon, 15 Sep 2014 22:44:08 GMT

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)

Re: Do loop to create new columns

micksom — Tue, 16 Sep 2014 07:23:30 GMT

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

Re: Do loop to create new columns

Loko — Tue, 16 Sep 2014 07:38:22 GMT

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

Re: Do loop to create new columns

RW9 — Tue, 16 Sep 2014 08:19:31 GMT

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?

Re: Do loop to create new columns

damanaulakh88 — Tue, 16 Sep 2014 10:14:39 GMT

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:

Re: Do loop to create new columns

AmySwinford — Wed, 17 Sep 2014 12:37:44 GMT

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