Using macro variables in PROC SQL case when statement

[brokenpc1]

Hi everyone,

I was originally using two strings in the PROC SQL case when statements:

case when Category in ('No 2024 Revenue', 'No Revenue 2024') then 'No Revenue'

    else 'Other'

end as Rev_category

I'd like to replace the year with a macro variable so I don't need to manually update it in the future. I created two macro variables as follows (I have defined "&CURRENT_YEAR" in previous steps):

%let CONDITION1 = %bquote(')No %sysfunc(compress(&CURRENT_YEAR.)) Revenue%bquote(');
%let CONDITION2 = %bquote(')No Revenue %sysfunc(compress(&CURRENT_YEAR.))%bquote(');

and updated the case when to:

case when Category in ("&CONDITION1", "&CONDITION2") then

'No Revenue'

    else 'Other'

end as Rev_category;

But this did not work. I figured maybe it's because when invoking the macro variables, the single quote did not work? But I am not sure. Could you please let me know what went wrong?

Thank you!

[mkeintz]

Consider using double quotes for the expressions used for the IN clause.  Unlike single quotes, they won't mask macro values.  Then

%let year=2024;

..... other code ....


proc sql ;
  select 
    .... other variables ...
    case 
      when Category in ("No &year Revenue", "No Revenue &year") then 'No Revenue'
      else 'Other'
    end 
    as Rev_category
  from ... yourdataset ;
quit;

Then all you have to do is change the "%LET year=" statement. 

[Tom]

Why did you use single quotes?  Those mask macro triggers from the macro processor.

case when Category in ("No 2024 Revenue", "No Revenue 2024") then 'No Revenue'
    else 'Other'
end as Rev_category

Now you can replace 2024 with a reference to the macro variable.

case when Category in ("No &CURRENT_YEAR. Revenue", "No Revenue &CURRENT_YEAR.") ...

If you are using the CASE clause in a pass through situation to a foreign database that only allows single quotes then you might want to use %BQUOTE() to allow you to resolve macro variable references inside of single quotes.  

case when Category in (%bquote('No &CURRENT_YEAR. Revenue', 'No Revenue &CURRENT_YEAR.'))

But watch out when trying to do that in regular SAS code.  You might need wrap it in a call to %UNQUOTE() to remove the macro quoting that %BQUOTE() added so the SAS parser does not get confused.

PS: Why did you feel a need to call the COMPRESS() function on your macro variable?  Did it have extra spaces in it?  Why?  Where did they come from? How did you assign the value to CURRENT_YEAR?  Make sure to use modern CALL SYMPUTX() function and not the ancient CALL SYMPUT() function.  And in PROC SQL when using the INTO clause to generate macro variables make sure to add the TRIMMED keyword.

[PaigeMiller · Accepted solution]

Please keep in mind that when you use macro variables and then run the program, SAS replaces the macro variables with their value. The resulting code must be legal valid working SAS code that does what you want.

So when &CURRENT_YEAR has value 2024 then &CONDITION1 has value 'No 2024 Revenue'. The quotes are part of the string, as is the actual text No 2024 Revenue. I think this is not what you want. Why? Because later your CASE WHEN statement becomes

case when Category in ("'No 2024 Revenue'", "'No Revenue 2024'") then

with the single quotes inside the double quotes. This probably doesn't work, as your character variable CATEGORY probably doesn't have single quotes surrounding the value as part of the text in variable CATEGORY. In general, I would not put macro variables in quotes, as you can see this gets complicated, and the code below probably works better (although since I don't have your data I can't be sure).

%let CONDITION1 = No %sysfunc(compress(&CURRENT_YEAR.)) Revenue;
%let CONDITION2 = No Revenue %sysfunc(compress(&CURRENT_YEAR.));

and when you run the code your CASE WHEN becomes

case when Category in ("No 2024 Revenue", "No Revenue 2024") then

which probably works properly.

General rule: when creating macro variables and assigning them values, do not put macro variables inside single quotes or inside double quotes. When using macro variables, if necessary. put them inside double quotes only (not single quotes).

Also as pointed out, COMPRESS is probably not needed but again I don't have your data so I can't be sure. So without COMPRESS, this simplifies even further to

%let CONDITION1 = No &CURRENT_YEAR. Revenue;
%let CONDITION2 = No Revenue &CURRENT_YEAR.;

[PaigeMiller]

Adding to my comment above:

It seems to be a poor choice to design your data such that the year NUMBER is inside a longer text string. This only complicates programming further. Better, in my opinion is to have two variables, numeric variable REVENUE which could contain a zero, or some other number; and YEAR which should be a number and ideally 4 digits (or just as good is to use a valid SAS date value*).

This makes programming much simpler, you then test to see if REVENUE = 0 and YEAR = 2024 (or YEAR=&CURRENT_YEAR). If you need to create output which then has a desired text string such as No Revenue 2024, you can do that after most of the programming is done but before you create the output table/graph/chart/whatever. In a DATA step use

text_string1 = cat('No Revenue ',year);

Then in the desired output part of the program, use variable TEXT_STRING1.

* — if using a valid SAS date in a variable named DATE, use

text_string1 = cat('No Revenue ',year(date));

[brokenpc1]

Thanks everyone for your help! I know a little better on how macro variables work in SAS now. 

In my query, I am joining an existing table with values such as "No 2024 Revenue", thus I need to write my code to match the column in the case when statement to generate a new column in my working dataset. 

Regarding why I use compress in the macro variable, I generated &current_year using a call:

data _null_;
call symput('YTD_MTH', MONTH(today() - 25));
call symput('CURRENT_YEAR', YEAR(today() - 25));
run;

I don't know why but when I invoke &current_year in my case when statement, the value is something like "No      2024 Revenue", that's why I am using compress to remove the lead blanks.

Thank you again for your help.

[PaigeMiller]

In my query, I am joining an existing table with values such as "No 2024 Revenue", thus I need to write my code to match the column in the case when statement to generate a new column in my working dataset. 

Yes, but don't design the data this way. Maybe you got the data from someone else, in which case you can't do anything about it, but if you created this existing table, don't do it this way with text "No 2024 Revenue''.

I don't know why but when I invoke &current_year in my case when statement, the value is something like "No      2024 Revenue", that's why I am using compress to remove the lead blanks.

Already answered above. Use CALL SYMPUTX().

[Tom]

Do NOT use the ancient CALL SYMPUT() routine unless you have some pressing need to generate macro variables that contain leading or trailing spaces.  That routine we replaced by the more powerful CALL SYMPUTX() over 20 years ago.  It knows how to convert numbers into the strings needed for storage into macro variables. Plus it has an optional third parameter to control the scope of which symbol table it places new macros variables when call inside a macro.

 1          data _null_;
 2            call symput('YTD_MTH', MONTH(today() - 25));
 3            call symput('CURRENT_YEAR', YEAR(today() - 25));
 4          run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       2:26   3:31   
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 5          
 6          %put YTD_MTH=|&YTD_MTH| CURRENT_YEAR=|&CURRENT_YEAR|;
 YTD_MTH=|          12| CURRENT_YEAR=|        2024|
 7          
 8          data _null_;
 9            call symputX('YTD_MTH', MONTH(today() - 25));
 10           call symputx('CURRENT_YEAR', YEAR(today() - 25));
 11         run;
 
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 12         
 13         %put YTD_MTH=|&YTD_MTH| CURRENT_YEAR=|&CURRENT_YEAR|;
 YTD_MTH=|12| CURRENT_YEAR=|2024|

If you need the leading zero in the YTD_MTD macro variable then add a PUT() function call to apply the Z format.

  call symputX('YTD_MTH', put(MONTH(today() - 25),Z2.));