How to use MACRO in PROC SQL to create consecutive variables in SAS

Question

I want to simplify the SAS code in PROC SQL by using MACRO. I already success in data step by using MARCO to replace the YYMM. The YYMM is consecutive for many months. The example data and my original code are as following.

data work.allredeem;
input no_code BONUS_0907 BONUS_0908   BONUS_0909   BONUS_0910   BONUS_0911   BONUS_0912   BONUS_1001   BONUS_1002   BONUS_1003   BONUS_1004   BONUS_1005   BONUS_1006   BONUS_1007   BONUS_1008   BONUS_1009   BONUS_1010   BONUS_1011   BONUS_1012;
DATALINES;
1   9   7   6   9   9   5   2   1   4   4   9   5   4   1   9   3   3   7
1   3   1   4   1   5   7   8   6   1   1   8   1   1   3   9   7   1   7
2   8   3   10  8   5   6   1   9   5   2   4   8   4   2   3   8   6   1
2   5   1   8   8   6   2   2   6   6   3   4   4   5   4   8   8   2   4
3   7   4   1   3   2   7   9   5   6   8   10  3   2   5   7   10  10  6
4   7   3   6   5   8   6   9   9   3   6   1   3   4   6   5   3   4   9
5   2   1   8   2   4   3   8   8   1   9   4   9   6   10  7   5   6   8
5   10  10  4   10  4   3   7   4   8   7   1   5   1   1   9   4   6   10
5   8   10  6   9   6   5   2   8   7   6   4   1   5   9   5   6   7   6
6   6   3   1   2   6   6   10  7   9   9   3   5   2   6   6   8   6   5
7   9   5   1   9   5   9   10  3   4   10  3   7   1   6   3   10  3   6
7   4   5   8   3   2   3   8   10  2   10  3   9   4   2   2   7   1   9
7   7   4   2   4   5   1   3   2   2   1   5   4   9   2   1   9   3   3
7   10  6   5   10  5   7   9   2   2   3   8   9   6   10  3   2   10  4
7   7   9   1   6   6   3   8   8   7   10  10  9   7   4   1   1   2   2
;
RUN;

PROC SQL;
create table allredeem2 as
SELECT no_code,  
sum(BONUS_0907) AS redeemNum0907,
sum(BONUS_0908)AS redeemNum0908,
sum(BONUS_0909)AS redeemNum0909,
sum(BONUS_0910)AS redeemNum0910,
sum(BONUS_0911)AS redeemNum0911,
sum(BONUS_0912)AS redeemNum0912,
sum(BONUS_1001)AS redeemNum1001,
sum(BONUS_1002)AS redeemNum1002,
sum(BONUS_1003)AS redeemNum1003,
sum(BONUS_1004)AS redeemNum1004,
sum(BONUS_1005)AS redeemNum1005,
sum(BONUS_1006)AS redeemNum1006,
sum(BONUS_1007)AS redeemNum1007,
sum(BONUS_1008)AS redeemNum1008,
sum(BONUS_1009)AS redeemNum1009,
sum(BONUS_1010)AS redeemNum1010,
sum(BONUS_1011)AS redeemNum1011,
sum(BONUS_1012)AS redeemNum1012
FROM allredeem
GROUP BY no_code;
QUIT;

I try to use MACRO as following but it does not work.

%MACRO  sub(year);
PROC SQL;
create table allredeem2 as
SELECT no_code,  sum(BONUS_&year) AS redeemNum&year 
FROM allredeem
GROUP BY no_code;
QUIT;
%MEND sub;
%sub(0907)
%sub(0908)
%sub(0909)
%sub(0910)
%sub(0911)
%sub(0912)
%sub(1001)
%sub(1002)
%sub(1003)
%sub(1004)
%sub(1005)
%sub(1006)
%sub(1007)
%sub(1008)
%sub(1009)
%sub(1010)
%sub(1011)
%sub(1012)

Thanks in advance.

Kiran Kiran · Accepted Answer · 2018-10-19T04:00:40

The reason the macro is not working is because you are creating the same table in your macro call, i.e. you are over writing the table in each of your macro call. In final table you will no_code and value pertaining to 1012. This things are easy to handle in proc means.

One way to handle in SQL this is using dictionary.columns as shown below.

proc sql noprint;
select 
'sum('|| trim(name)||') as redeemNum'||compress(name,,'kd') into :sum separated by 
 ','
from dictionary.columns
where libname ='WORK'
and upcase(memname) =  upcase('allredeem')
and upcase(type) ='NUM'
and upcase(name) ne upcase("no_code");
%put &count;

proc sql;
create table want as 
select no_code, &sum
 from allredeem
 group by no_code;

Another easy way to handle is to use array and sum statement in datastep.

  proc sort data=allredeem;
  by no_code;
  run;


data want;
set allredeem;
by no_code;
array bonus(*) BONUS0907 - BONUS0912   BONUS1001 - BONUS1012;
array redeem(*) redeemNum0907 - redeemNum0912   redeemNum1001 - redeemNum1012;
do i = 1 to dim(bonus);
if first.no_code then redeem(i) = bonus(i);
else redeem(i) +bonus(i);
end;
drop bonus: i;
if last.no_code;
run;

How to use MACRO in PROC SQL to create consecutive variables in SAS

1 Answers