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.
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;