BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SOF1_CWoPA
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION
4 REPLIES 4
ballardw
Super User

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
Fluorite | Level 6

Many thanks for the reply and recommendations!! 

 

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

SOF1_CWoPA
Fluorite | Level 6

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

 

Have a great weekend everyone.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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