How do I create an automatic multi macro?

[_SASEG_]

Hello!

I am using SAS Enterprise Guide 7.1.

I am trying to create a macro that automatically pulls ids from a dataset and then the next bit of code selects the data associated with those ids. The current macro works fine, but only selects 1 id. How do I tweak it to allow multiple values?

On a separate note, I know how to create a list of ids in a macro, but it is manual, by using:

%let id = ('123', '456', '789')

I then add this into my code:

WHERE id IN &id.

Is it possible to do something similar but automated?

The above code I use in PROC SQL. The code that I am using currently is a data step:

data _null_;
set test;
call symput('id1',id1);
run;

%put _user_;

data test2;
set Cues (where=(id = &id1.));
run;

Not sure if it possible, maybe there is a different way?

Thanks in advance!

[PaigeMiller]

I am trying to create a macro that automatically pulls ids from a dataset and then the next bit of code selects the data associated with those ids. The current macro works fine, but only selects 1 id. How do I tweak it to allow multiple values?

Could you provide more explanation, more detail and so on? How would the macro know which IDs to pull from a data set?? Right now you have hard-coded the IDs, that doesn't really help us in designing a macro.

[Tom]

I don't see any macro code in your example.  Did you mean you want to create a macro variable instead of defining a macro?

If you want to put a list of values into a macro variable it is easiest to use the SEPARATED keyword in the INTO clause of the SELECT statement in PROC SQL.  So if you have a dataset named TEST with a variable named ID1 you can put all of the values into a single macro variable like this:

proc sql noprint;
select distinct quote(trim(id1)) into :idlist separated by ' ' from test;
quit;

You can then use the list of quoted values of ID1 with the IN operator.

data test2;
  set Cues;
  where id = (&idlist.);
run;

[_SASEG_]

Thanks for responding @Tom.

Apologies, I thought the function symput created a macro.

Your code makes sense. The field I am using for id is actually numeric. How would I alter the code, as I have received the following error:

ERROR: Function TRIM requires a character expression as argument 1.

Thanks!

[Tom · Accepted solution]

To define a macro you use the %MACRO and %MEND statements.   To call a macro you use % in front of the macro name.  %mymacro().

To create a macro variable you can use %LET statement or the CALL SYMPUTX() function (do not use the archaic CALL SYMPUT() function unless you really need to have leading and/or trailing spaces added to the value of the macro variable).   To reference a macro variable you use & in front of the macro variable's name.  &myvar.

If the values of ID1 are numeric then do not use the QUOTE(TRIM()).   But if the values included decimal part or are integers larger than 12 digits then make sure to tell PROC SQL how you want them converting into strings.

select distinct id1 format=best32. into :idlist separated by ' ' from test;

If the values of ID1 are numeric and the values of ID are character then you will probably want to first convert ID1 to character string before adding the quotes.  Note you also need to worry that the strings in ID are formatted exactly the same as the way you are converting ID1 into strings.

select distinct quote(strip(put(id1,best32.))) into :idlist separated by ' ' from test;

[_SASEG_]

Thanks so much Tom! Those are great tips!!

My code now works perfectly!