topic Re: Using a macro for a character number. in SAS Programming

Using a macro for a character number.

SASGeek — Thu, 12 Jan 2023 15:44:53 GMT

Hello all,

Sorry for the confusing title -- not sure how to phrase it.

In the code below, I'm looking into a Netezza table (Data step won't work) and deleting current period records if they exist. The issue is, how to represent the value of 202212 as ym_id is a character/text field (don't ask me, I didn't create the database).

If I hard-code '202212' then it works
I need to keep &period as 202212 since it's used throughout the program.
If I use &period then I get ERROR: Expression using equals (=) has components that are of different data types.
If I use &qperiod it resolves to '202212' but then I get the error ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, ALL, ANY, BTRIM, CALCULATED, CASE, INPUT, PUT, SELECT, SOME, SUBSTRING, TRANSLATE, USER

Would anyone help me please with this macro issue?

Thank you

/******** CODE IN QUESTION **************/

%let period = 202212;

%let qperiod = %str(')&period%str(');

proc sql noerrorstop;

delete from core.mytable where ym_id = &period;

quit;

Re: Using a macro for a character number.

PaigeMiller — Thu, 12 Jan 2023 15:49:16 GMT

You can't have imbalanced quotes inside %STR() See https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/p0pnc7p9n4h6g5n16g6js048nhfl.htm

You can have imbalanced quotes if you use the % operator inside %STR()

%let qperiod = %str(%')&period%str(%');

Re: Using a macro for a character number.

Tom — Thu, 12 Jan 2023 15:58:40 GMT

The simple solution is to put the quotes into the macro variable to begin with:

%let period = '202212';

If you are building the macro variable with SAS code use the QUOTE function to add the single quotes.

data _null_;
  call symputx('period',quote(put(today(),yymmn6.),"'");
run;

If you really really have to have the macro variable without quotes then use the %BQUOTE() function to add them.

%let period=202212;
... 
where ym_id = %bquote('&period')
...

1894  %let period=202212;
1895  %put
1896  where ym_id = %bquote('&period')
1897  ;
where ym_id = '202212'

Re: Using a macro for a character number.

ballardw — Thu, 12 Jan 2023 16:04:07 GMT

In a large percentage of cases it is better not to make quotes part of the macro variable at all. Use double quotes around a macro variable to allow it to resolve:

%let period = 202212;

proc sql noerrorstop;
    delete from core.mytable where ym_id= "&period.";
quit;

That means for the places where &period may not want quotes you don't need another variable to have that available.

With that said if period 202212 is supposed be December of 2022 then you may want to rethink entirely how that is stored. That really should be a date value and character values holding dates almost always require using date values for manipulation unless it is trivial. For example what if you want to represent a duration of 3 sequential months?

You can't use something like

ym_id in ("&period" "%eval(&period. +1)" "%eval(&period. +2")  )

because +1 would be 202213, not the desired 202301.

Re: Using a macro for a character number.

Astounding — Thu, 12 Jan 2023 17:47:28 GMT

There are cases within SQL where a quoted quote causes a problem. If all the above suggestions are still not giving you a solution, try:

%let qperiod = %unquote(%str(%')&period%str(%'));

and be sure to use &qperiod where needed, not &period,.