DATA Step, Macro, Functions and more

Using Macro in Do Loop Where Assignments are Sets

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Using Macro in Do Loop Where Assignments are Sets

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.


Accepted Solutions
Solution
‎02-02-2018 01:03 PM
Super User
Posts: 10,532

Re: Using Macro in Do Loop Where Assignments are Sets

Posted in reply to SOF1_CWoPA

To indirectly address macro variables, use a double ampersand:

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

            a.field3 in &&set_&k.

 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 13,898

Re: Using Macro in Do Loop Where Assignments are Sets

Posted in reply to SOF1_CWoPA

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

 

New Contributor
Posts: 3

Re: Using Macro in Do Loop Where Assignments are Sets

Many thanks for the reply and recommendations!! 

 

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

Solution
‎02-02-2018 01:03 PM
Super User
Posts: 10,532

Re: Using Macro in Do Loop Where Assignments are Sets

Posted in reply to SOF1_CWoPA

To indirectly address macro variables, use a double ampersand:

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

            a.field3 in &&set_&k.

 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: Using Macro in Do Loop Where Assignments are Sets

Posted in reply to KurtBremser

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

 

Have a great weekend everyone.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 139 views
  • 2 likes
  • 3 in conversation