Macro processing in SAS Proc Sql


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;
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aloysius LowCommented:
Your could enclose the case statement with a do loop and change all &var1 to &&var&i
%do i = 1 %to 100; /* 100 or whatever ending number you wish */
case when a1.&&var&i ne s1.&&var&i then  1 else 0 end as &&var&i.._ck,

however, before that can work, you'll need to assign the column names into the macro variables var1 to var100 (or whatever number that is).

to do so, you could either do 100 %let statements, e.g.
%let var1 = totweight;
%let var2 = ALDH;
OR you could have all the column names in a table, which you then use call symput to assign the value in i.e.
data _null_;
  set columns end = eof; /* columns is the name of the dataset containing all columns you want to assign into the macro variable varX to be used in the loop above */
  call symput(compress("var" || put(_N_, best.)), trim(left(colname))); /* colname is the name of the column that stores the names of the columns you want to assign into the macro variable varX */
  if eof then do;
    call symput("total_cols", trim(left(put(_N_, best.)))); /* assign the number of columns into a macro variable total_cols to be used in the loop - not required if you want to hard-code the number of times to loop, or if you wish to pass in the number of times to loop into the macro - useful when some tables have more columns than the others, but the common columns are all in front */

how i would rewrite the code (assuming the number of times to loop is assigned into total_cols above using call symput:
%do i = 1 %to &total_cols;
a1.&&var&i ne s1.&&var&i as &&var&i.._ck,

in this case, there is actually no need to use the case statement as the check itself (e.g. a1.totweight ne s1.totweight) would result in a 1 or 0 value.

very important note: to end it off, i would put a dummy column after the loop statement. this is because the loop is at the end and the final column in the select statment will have a comma, since the comma is being coded into the loop. of course, you can have a %if-%then-%else statement to put the comma, but i'm lazy so i'll just put any column that has appeared before (e.g. A1.memberid) post the loop i.e.
%do i = 1 %to &total_cols;
a1.&&var&i ne s1.&&var&i as &&var&i.._ck,

SAS will issue a warning to say that memberid is being repeated and will not be included in the final output. if that bothers you, then you could do
%do i = 1 %to &total_cols;
a1.&&var&i ne s1.&&var&i as &&var&i.._ck
  %if &i lt &total_cols %then %do;

let me know if this helps, or if you have any further questions/errors encountered (p.s. i wrote this without a programming environment to test, so error might pop up here or there ;))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moriniaAdvanced Analytics AnalystAuthor Commented:

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;
Aloysius LowCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.