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

I am reading a user input variable for the 2 digit abbreviation of a state.  I am testing the length of the variable to see if it is > 0 then I know the user entered a value, for example 'MD'. The variable in my title line in the report would show 'MD'.

 

If the user does not enter a value and it is blank, in my code I am trying to set it to the value of 'ALL'.  If this prompt is left blank then the user wants to get results for all states.

 

%macro BuildWhereClause;
   %global where_clause;
   %let where_clause = ;

/* check prompt STATE for user entered values */
  %if %length(&STATE) > 0 %then %do ;        /* value entered in prompt */
      %let NEW_STATE = "&STATE";             /* wrap quotes around macro variable */
      %let where_clause = &where_clause AND UPCASE(t1.STATE) = UPCASE(&NEW_STATE); /* build where clause*/
  %end;
  %else %do;   
%let NEW_STATE = call %symput("&STATE","ALL "); %mend; %BuildWhereClause;

The code runs, however my variables have these values when I do %put to display.  It is not executing the symput to resolve the variable with correct value

STATE =

WARNING: Apparent invocation of macro SYMPUT not resolved.

NEW_STATE = call %symput("","ALL ") 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

You should be able to do it this way:

 

%else %do;

   %let NEW_STATE = "ALL ";

%end;

 

It's not clear why you need quotes around "ALL" for this application, but that depends on what will be happening later with &NEW_STATE.

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, this statement:

%let NEW_STATE = call %symput("&STATE","ALL ");

Is incorrect to start with, you do not call a macro - % means its a macro.  So maybe you need call symput.  

 

However the question has to be why do this at all?  If you show your actual code where you use it will be clearer, but it seems to me:

from  ABC
where  AVAR=COALESCE("&STATE.","ALL");

Should work fine, i.e. if &STATE. is null then "ALL" will be used, otherwise &STATE. will be used.  As I always say, don't do your coding in macro, it is only there to generate code, Base SAS is the programming language to manipulate and process data!

ncsthbell
Quartz | Level 8

I do not have a lot of experience in SAS so the way you mentioned to do it in the 'where' clause with the functions, I have never used them before.  When there are SO many ways to do things in SAS, it's hard to know which is the best.  As I said, I don't have a lot of experience.

 

I removed the '%' from my call symput and it still did not resolve.   I do not what to put this in my where clause because I have about 20 prompts, this is only one.  I have already written the code, this is the only peice I can't get to work.

 

Thanks for you suggestions.  I will take a look at trying this when I start the next project!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Unfortunately if you don't know Base SAS - which is the programming language - I don't see how learning macro will help you.  Macro language's only purpose is to generate Base SAS code, it does nothing else.  It has nothing to do with which method is best, it all compiles to Base SAS code at the end of the day.

 

This seems to work - note that in your code you are missing a %end:

%macro BuildWhereClause;
  %global where_clause;
  %let where_clause=;
  %let new_state=abc;
  %let state=def;
  %if %length(&STATE.) > 0 %then %do;
      %let NEW_STATE = "&STATE.";
      %let where_clause=&where_clause. AND UPCASE(t1.STATE) = UPCASE(&NEW_STATE.);
  %end;
  %else %do;   
      %let NEW_STATE = call %symput("&STATE","ALL ");
  %end;
%mend;
%BuildWhereClause; 

%put &where_clause.;
Kurt_Bremser
Super User

You cannot use call symput in macro code, as it is data step language. call symput is meant ONLY to set macro variables during data step runtime.
To set a macro variable, simply use %let (which is valid in macros and "open code")

 

to append to a macro variable, simply do

%let var = &var.xxxxxx;

where xxxxxx is the text you want to append.

Astounding
PROC Star

You should be able to do it this way:

 

%else %do;

   %let NEW_STATE = "ALL ";

%end;

 

It's not clear why you need quotes around "ALL" for this application, but that depends on what will be happening later with &NEW_STATE.

ballardw
Super User

It might help to show the context of using this macro as well. Since you name it "BuildWhereClause" then I would expect it to make the appropriate where clause (even a blank one) for all of the expected inputs.

 

 

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