Using Macro in Do Loop Where Assignments are Sets

[SOF1_CWoPA]

Hello.  This is the first time I'm posting a question and am a rank beginner with SAS 9.4, so I hope this doesn't sound stupid.

I'm trying to use a macro where the variable assignments are sets of numbers in a do loop.  So, for example, I'd have

%let set_1 = (123, 234, 345);

%let set_2 = (456, 567, 678);

%let set_3 = (789, 890, 012);

Then a proc sql statement like:

%macro loop(Start,End);
  %DO k=&Start. %TO &End.;
    Proc SQL;
      Create table work.rst_&k. as
         Select
            a.field
            sum(a.abc) as W,
            sum(a.bcd) as Z,

         From  work.data1 a, work.data2 b,

         Where
            a.field1 = b.field1 and
            a.field2 = b.field2

            a.field3 in set_&k.

         Group by a.year

         Order by a.year
      ;
    %End;
    quit;
%mend;
%loop (Start=1, End=3)

The set_x are much larger than displayed here, and there are far more sets.

The way I did it originally was to do a separate SQL step for each set_x, and that worked, but I was hoping I could streamline the code a bit.

The error I'm getting with the Do Loop is:

WARNING: Apparent symbolic reference Set_ not resolved.
NOTE: Line generated by the invoked macro "LOOP".
3 &set_&k. 

-

22

76
ERROR 22-322: Syntax error, expecting one of the following: (, SELECT.

ERROR 76-322: Syntax error, statement will be ignored.

Thank you for any help you are willing to provide and please advise if I put this request in the wrong place.

[ballardw]

I see three serious issues and one minor:

Proc SQL;
      Create table work.rst_&k. as
         Select
            a.field                    <missing comma
            sum(a.abc) as W,
            sum(a.bcd) as Z,  < This comma should not be here as 'from' is now treated as a variable to select

         From  work.data1 a, work.data2 b, < This comma should not be here as where is going to be treated as dataset

         Where
            a.field1 = b.field1 and
            a.field2 = b.field2

            a.field3 in set_&k.

         Group by a.year

         Order by a.year
      ;
    %End; < this is a minor issue, I don't see a real reason not to use  " quit; %end;" to generate complete Proc SQL calls inside the loop. 
    quit;

Obviously we do not know how big your two data sets are that you use in your from statement but the number of full Cartesian joins you may do can get very expensive in time for repeated actions.

If this were my data I don't think I would need a macro at all as long as none of your Set_x lists over lap. I would do something to assign a LIST value with one pass through the data. Depending on how you currently have your list information that could be used to make a format which could be used with a : put(a.field3,mylistformat) as ListId

Something like (note: no macro)

proc format library=work;
value mylistid
123, 234, 345 = '1'
456, 567, 678 = '2'
789, 890, 012 = '3'
other = '0' /* or what might make sense*/
;
run;
Proc SQL;
   Create table work.rst_lists as
      Select
         a.field,
         sum(a.abc) as W,
         sum(a.bcd) as Z,
         put(a.field3,mylistid.) as ListId
      From  work.data1 a, work.data2 b,

      Where
         a.field1 = b.field1 and
         a.field2 = b.field2
         
      Group by a.year
      Order by a.year
   ;
 quit;

You might add Listid to the order

[SOF1_CWoPA]

Many thanks for the reply and recommendations!! 

Will this generate a separate data set in the work library for each of '1', '2', ...?

[Kurt_Bremser · Accepted solution]

To indirectly address macro variables, use a double ampersand:

Where
            a.field1 = b.field1 and
            a.field2 = b.field2

            a.field3 in &&set_&k.

 

[SOF1_CWoPA]

That's perfect!!  Thank you so much!!!

Have a great weekend everyone.