Macro processing in SAS Proc Sql
Experts,
I have the following code below. The challenge I have is that I have about 100 columns that I want to create and assign a value as in the case statement below. Is there a way I can put the variables in a macro and repeat the code or some other way to check all the variables so I can just feed the list of variables in without making 100 case statements.
%macro rep(var1);
Proc sql;
Create table member_diffs1 as
Select A1.memberid
, N1.mbr_last_nm AS 'MEMBER LAST NAME'n
, N1.mbr_first_nm AS 'MEMBER FIRST NAME'n
, N1.member_id format=Z8. AS 'MEMBER ID'n
, N1.mbr_product_cd AS 'PRODUCT CODE'n
, A1.rate_cell_desc as Rate_Desc
, case
when a1.&var1 ne s1.&var1 then 1 else 0
end as &var1._ck
from Table1 A1, Table2 S1, Names N1, member_diffs D1
where A1.memberid=S1.memberid and A1.memberid=N1.memberid and A1.memberid =D1.memberid;
quit;
%mend;
%rep(totweight);
%rep(ALDH);
I have the following code below. The challenge I have is that I have about 100 columns that I want to create and assign a value as in the case statement below. Is there a way I can put the variables in a macro and repeat the code or some other way to check all the variables so I can just feed the list of variables in without making 100 case statements.
%macro rep(var1);
Proc sql;
Create table member_diffs1 as
Select A1.memberid
, N1.mbr_last_nm AS 'MEMBER LAST NAME'n
, N1.mbr_first_nm AS 'MEMBER FIRST NAME'n
, N1.member_id format=Z8. AS 'MEMBER ID'n
, N1.mbr_product_cd AS 'PRODUCT CODE'n
, A1.rate_cell_desc as Rate_Desc
, case
when a1.&var1 ne s1.&var1 then 1 else 0
end as &var1._ck
from Table1 A1, Table2 S1, Names N1, member_diffs D1
where A1.memberid=S1.memberid and A1.memberid=N1.memberid and A1.memberid =D1.memberid;
quit;
%mend;
%rep(totweight);
%rep(ALDH);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well if your data is small, there's little harm in doing so, only more irritating to add a column at each go. but when you are processing millions of rows, doing that is going to slow things down, as you are going to do the same join hundreds of times, when it need only be done once
ASKER
The easy way out I found was to run the macro and just add the variables. I didn't think that would work, but it did. I created the base table and just kept adding one variable at a time.
Once the table was created with the first 5 variables I wanted. I just kept adding to the table with the code below. I was able to create all 100 additional columns. I did get the warning about Create table recursively referencing the Target table, but that is fine.
%macro rep(var1);
Proc sql;
Create table diffs as
Select D1.*
, case
when a1.&var1 ne s1.&var1 then 'X' else ""
end as &var1._ck
from Names N1, Current A1, Diffs D1, State S1
where A1.memberid=S1.memberid and A1.memberid=N1.memberid and A1.memberid =D1.memberid;
quit;
%mend;
%rep(var1);
%rep(var2);
%rep(var3);
%rep(var4);
.