topic Can not get macro variable to resolve to correct value. in SAS Programming

Can not get macro variable to resolve to correct value.

ncsthbell — Tue, 22 Mar 2016 15:16:23 GMT

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

Re: Can not get macro variable to resolve to correct value.

RW9 — Tue, 22 Mar 2016 15:24:13 GMT

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!

Re: Can not get macro variable to resolve to correct value.

ncsthbell — Tue, 22 Mar 2016 15:30:42 GMT

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!

Re: Can not get macro variable to resolve to correct value.

Astounding — Tue, 22 Mar 2016 15:38:21 GMT

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.

Re: Can not get macro variable to resolve to correct value.

RW9 — Tue, 22 Mar 2016 15:49:19 GMT

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

Re: Can not get macro variable to resolve to correct value.

Kurt_Bremser — Tue, 22 Mar 2016 17:12:13 GMT

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.

Re: Can not get macro variable to resolve to correct value.

ballardw — Tue, 22 Mar 2016 17:35:07 GMT

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.