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

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

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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);

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

13 REPLIES 13
PaigeMiller
Diamond | Level 26

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
cbrotz
Pyrite | Level 9

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.

 

 

Astounding
PROC Star

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.

mkeintz
PROC Star

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);

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
cbrotz
Pyrite | Level 9

Worked perfectly!  Thanks

cbrotz
Pyrite | Level 9

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!

Tom
Super User Tom
Super User

@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.

cbrotz
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

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...

 

 

Tom
Super User Tom
Super User

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.

 

ScottBass
Rhodochrosite | Level 12

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...

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
cbrotz
Pyrite | Level 9

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.

         

ScottBass
Rhodochrosite | Level 12

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.

 

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

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
  • 13 replies
  • 5757 views
  • 5 likes
  • 6 in conversation