DATA Step, Macro, Functions and more

How to code for multiple macro variable values?

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

How to code for multiple macro variable values?

I have code that uses input from an SAS EG prompt.  There can be multiple values - over 100 that could be selected.  I am looking for a way to code for whatever number come thorugh.  The code below allows me to use three.  I am hoping to not have to code for all 100+.

 

This is the log:

 

    %LET _SASPROGRAMFILEHOST='';
10         %LET LEVEL20 = 4;
11         %LET LEVEL21 = PLACARDS;
12         %LET LEVEL22 = POSTERS;
13         %LET LEVEL23 = NEWSLETTER BINDERS;

 

I am currently using these variables like this to select in my SQL code:

 

 WHERE t1.LEVEL2 IN ("&LEVEL21","&LEVEL22","&LEVEL23");

 

How do I code for any amount that may come through?

 

Thanks


Accepted Solutions
Solution
‎09-29-2017 02:24 PM
Trusted Advisor
Posts: 1,294

Re: How to code for multiple macro variable values?

[ Edited ]

Use the dictionary.macros tools in proc sql, to get a comma separated list of quoted values for macrovars with names starting with LEVEL2.

 

proc sql noprint;
  select distinct cats('"',value,'"')
  into :value_list separated by ','
  from dictionary.macros
  where name like 'LEVEL2%';
quit;

 

Then you can use

where t1.level2 in (&value_list);

 

View solution in original post


All Replies
Respected Advisor
Posts: 2,661

Re: How to code for multiple macro variable values?

Are these macro variables really created by %let commands? Do the values come from a database or SAS data set? Where do the values come from? Where do the macro variable names come from?

--
Paige Miller
Contributor
Posts: 50

Re: How to code for multiple macro variable values?

Posted in reply to PaigeMiller

The values come from a SAS EG Prompt routine....I attached a screenshot.  The values in the prompt come from another table.  I think it has a routine that just assigns the names based on the root name in the prompt from the looks of it.

 

 

Super User
Posts: 6,543

Re: How to code for multiple macro variable values?

[ Edited ]

Here's an idea you can play with, but you will need to do extensive testing.  For example, what happens if a user goes through the prompt sequence multiple times and enters fewer prompts the second time.

 

DICTIONARY.MACROS contains the names and values of all existing macro variables.  If memory serves (can't check it right now), the key variables from your point of view would be NAME and VALUE.  So you could experiment with:

 

t1.LEVEL2 in (select distinct '"' || trim(value) || '"' from dictionary.macros where upcase(name) contains ("LEVEL2%") )

 

You need to find a way to modify this idea, to omit including "4" as one of the items in the list, although that may not really matter in practice if your list contains that extra numeric value in it.

 

I'm not sure at all that I got the syntax right here, but I definitely have a workable approach.  You may need to break this  into two steps, to extract the quoted list into a single macro variable first and then use that single macro variable in your current query.

Solution
‎09-29-2017 02:24 PM
Trusted Advisor
Posts: 1,294

Re: How to code for multiple macro variable values?

[ Edited ]

Use the dictionary.macros tools in proc sql, to get a comma separated list of quoted values for macrovars with names starting with LEVEL2.

 

proc sql noprint;
  select distinct cats('"',value,'"')
  into :value_list separated by ','
  from dictionary.macros
  where name like 'LEVEL2%';
quit;

 

Then you can use

where t1.level2 in (&value_list);

 

Contributor
Posts: 50

Re: How to code for multiple macro variable values?

Worked perfectly!  Thanks

Contributor
Posts: 50

Re: How to code for multiple macro variable values?

One more quick question on this code....should it work the same for numeric values?  I can get the value list but the

 

where t1.level2 in (&value_list);

is looking for character data or something and I keep getting an error when I use numeric on a material code:

 

proc sql noprint;
  select distinct cats('"',value,'"')
  into :value_list2 separated by ','
  from dictionary.macros
  where name like 'MATERIAL%';
quit;

 

 %LET Material1 = 5784;
24         %LET Material2 = 8001108;
25         %LET Material3 = 409400;

 

where t1.MATERIAL IN (&value_list2);

 

Thanks!

Super User
Super User
Posts: 7,860

Re: How to code for multiple macro variable values?


cbrotz wrote:

One more quick question on this code....should it work the same for numeric values?  I can get the value list but the

 

where t1.level2 in (&value_list);

is looking for character data or something and I keep getting an error when I use numeric on a material code:

 

proc sql noprint;
  select distinct cats('"',value,'"')
  into :value_list2 separated by ','
  from dictionary.macros
  where name like 'MATERIAL%';
quit;

 

 %LET Material1 = 5784;
24         %LET Material2 = 8001108;
25         %LET Material3 = 409400;

 

where t1.MATERIAL IN (&value_list2);

 

Thanks!


That method is not really going to work reliably.

  1. The values that you see in the DICTIONARY.MACROS view include the special characters SAS uses for macro quoting.
  2. The values are wrapped onto multiple observations when they exceed 200 characters.
  3. If you are using SAS prompts then it is creating other macro variables that will match that pattern in the LIKE condition.
  4. You could also have created other macro variables that would match your pattern.

Is your dataset variable numeric or character?  If the variable is numeric then you don't want string literals in your IN () list so don't add the quotes.

Contributor
Posts: 50

Re: How to code for multiple macro variable values?

Ok I am sure you are right but it is working okay so far.  Are you saying if one selection exceeds 200 characters or in total? Also, as long as I am the one naming the macros, and don't overlap, etc. wouldn't that control the pattern?  Yes taking the quotes out worked - thanks.    I saw the link you had to a more complicated solution.  I will take a look at that and see if I can follow it.

Super User
Super User
Posts: 7,860

Re: How to code for multiple macro variable values?

SAS EG (and other such tools) will create a series of macro variables when you specify that users are allowed to selection multiple values.  You might consider writing a little utility macro to take advantage of that.

I think that EG might actual provide you with one, but here is another thread on this side has a macro.  For your situation make sure to update the macro to add the quotes needed for string literals.

https://communities.sas.com/t5/General-SAS-Programming/Do-loop-using-a-prompt-generated-macro-variab...

 

 

Super User
Super User
Posts: 7,860

Re: How to code for multiple macro variable values?

[ Edited ]

Here is a macro that should handle converting a series of macros into a series of string literals. It should handle most of the wrinkle that SAS prompt present like not populating &prompt.1 when there was only one selection. Or populationg &prompt.count instead of &prompt.0 with the number of selections.

%macro qprompt(prompt);
%local i ;
%if %symexist(&prompt._count) and not %symexist(&prompt.0) %then %let &prompt.0=&&&prompt._count;
%if %symexist(&prompt) and not %symexist(&prompt.1) %then %do;
  %let &prompt.1=&&&prompt;
  %let &prompt.0=1;
%end;
%do i=1 %to &&&prompt.0;  %sysfunc(quote(&&&prompt.&i)) %end;
%mend qprompt ;

So for your example (with corrected count)

%LET LEVEL20 = 3;
%LET LEVEL21 = PLACARDS;
%LET LEVEL22 = POSTERS;
%LET LEVEL23 = NEWSLETTER BINDERS;

The prompt (or base macro varible name) is LEVEL2. So you could use this macro in your WHERE clause

where level2 in (%qprompt(level2))

So that would generate this WHERE clause with that data.

where level2 in ("PLACARDS"  "POSTERS"  "NEWSLETTER BINDERS")

Note that the IN operator in SAS does not need commas between the elements so it is easier to just not generate them.

 

Super Contributor
Posts: 392

Re: How to code for multiple macro variable values?

[ Edited ]

While some of the previous approaches work, since the EG prompting manager creates macro variables in a known naming convention, below is another approach.

 

I only wish EG created a "proper" macro variable array, rather than leaving off the "1" suffix when only a single value is allowed.  IMO a poor design decision.

 

Go here:  https://github.com/scottbass/SAS/tree/master/Macro, and download stp_seplist, seplist, and parmv.  Put them in your sasautos location.

 

Then, create your EG prompts, and configure %stp_seplist appropriately. 

 

I created an EG prompt called LEVEL, Prompt type Text, User selects values from a static list, Multiple values, List of values:  PLACARDS, POSTERS, NEWSLETTER BINDERS.

 

I associated that prompt with an EG program entry containing:

 

%put %stp_seplist(level,nest=qq);

 

My prompt values (macro variables) were:

 

8 %LET LEVEL0 = 3;
9 %LET LEVEL1 = PLACARDS;
10 %LET LEVEL = PLACARDS;
11 %LET LEVEL_count = 3;
12 %LET LEVEL2 = POSTERS;
13 %LET LEVEL3 = NEWSLETTER BINDERS;

 

My program results were:

 

30 %put %stp_seplist(level,nest=qq);
"PLACARDS","POSTERS","NEWSLETTER BINDERS"

 

The macro default is double quoted strings (so character data). 

 

For numeric data, either invoke as: 

 

%put %stp_seplist(num,nest=);

 

or change the macro's default value for NEST=.

 

Experiment with different prompt types and see how you go.

 

Hope this helps...

 

Contributor
Posts: 50

Re: How to code for multiple macro variable values?

Posted in reply to ScottBass

So I think I am seeing the issue as I have run these macros a few more times......I just want to be clear what is happening.

 

I made a few selections for three variables Level2 Level3  (both character)  and Material which is numeric.

 

 

%LET LEVEL20 = 2;
11         %LET LEVEL21 = POSTERS;
12         %LET LEVEL22 = PLACARDS;
13         %LET LEVEL2_count = 2;
14         %LET LEVEL2 = POSTERS;
15         %LET LEVEL3_count = 1;
16         %LET LEVEL3 = MAINT LABELS;
17         %LET Material_count = 1;
18         %LET Material = 5784;

 

So for Level2 the results are 2, POSTERS, PLACARDS

          Level3 the results are 1, MAINT LABELS

          Material the results are 1, 5784

 

In this particular situation, I am comparing these results in one more step using a left join back to a master list.  The Level2 value 2 will drop because 2 is  not a valid value.  Level3 1 will drop because it is also not valid.  I see an issue with my data in the Material example because 1 is valid and I am getting data that was not asked for.  I could have them type in a Material Description instead and that would eliminate the numeric issues....if i am following this correctly?  This feature could be very useful....I was hoping to get the numeric piece to work as well and will play around with this some more.  Thanks again.

         

Super Contributor
Posts: 392

Re: How to code for multiple macro variable values?

[ Edited ]

You don't explain how you generate "the results", but stop including the count variable in "the results". It's not part of your IN() list.

 

Edit:

Submit the below code and see if it meets your needs:

 

%LET LEVEL20 = 2;
%LET LEVEL21 = POSTERS;
%LET LEVEL22 = PLACARDS;
%LET LEVEL2_count = 2;
%LET LEVEL2 = POSTERS;
%LET LEVEL3_count = 1;
%LET LEVEL3 = MAINT LABELS;
%LET Material_count = 1;
%LET Material = 5784;

%let where=
t1.LEVEL2 IN (%stp_seplist(LEVEL2,nest=qq))
and
t1.LEVEL3 IN (%stp_seplist(LEVEL3,nest=qq))
and
t1.Material IN (%stp_seplist(Material,nest=))
;

%put &where;

* use intermediate variables if the leading spaces bother you ;
%let lev2=%stp_seplist(LEVEL2,nest=qq);
%let lev3=%stp_seplist(LEVEL3,nest=qq);
%let mat=%stp_seplist(Material,nest=);

%let where=
t1.LEVEL2 IN (&lev2)
and
t1.LEVEL3 IN (&lev3)
and
t1.Material IN (&mat)
;

%put &where;

There are some leading spaces in the IN list due to how the macro processor parses the data, but the code is syntactically correct.  You can use intermediate macro variables if that's a big deal for you.

 

 

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 693 views
  • 5 likes
  • 6 in conversation