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 ")
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.
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!
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!
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.;
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.
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.
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.