topic Re: convert macro value to numeric in SAS Programming

convert macro value to numeric

Batman — Tue, 27 Jun 2023 18:48:33 GMT

Can anyone suggest a fix to this code? I'm trying to divide the value yr17 by the macro variable value &yr17

27 proc sql noprint;
28 select yr17 into :yr17
29 from T10_1
30 where Group=1;
31 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

32
33 options symbolgen;
34 proc sql;
35 select put(Group,Group_num.) as Group, Value, yr17, %eval(yr17/&yr17) as Pct17
SYMBOLGEN: Macro variable YR17 resolves to 4,618
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
yr17/ 4,618
35 select put(Group,Group_num.) as Group, Value, yr17, %eval(yr17/&yr17) as Pct17
_____
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

2 The SAS System 14:03 Tuesday, June 27, 2023

36 from T10_1
37 where Group=2;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
38 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: convert macro value to numeric

Astounding — Tue, 27 Jun 2023 18:59:07 GMT

The %EVAL function cannot be applied to a DATA step variable such as yr17. Just remove it (and the related parentheses).

Re: convert macro value to numeric

PaigeMiller — Tue, 27 Jun 2023 19:12:07 GMT

First, just to be technically correct, macro variables are always text. There is no such thing as a numeric macro variable. (They may look like numbers to humans, but they are text).

Next, the macro processor just replaces macro variables with their value, and the result MUST BE valid legal working SAS code. So if your macro variable has value 4,618, when this replaces the macro variable, you get yr17/4,618 which is NOT legal valid working SAS code. Can you see why this doesn't work??? It has nothing to do with macro variables.

So, the problem is that the value of variable YR17 in data set T10_1 is not a valid number, it is a character string '4,618'. So that's where you need to do the conversion to a character string without commas (or other non-digits); or create a numeric variable with value 4618 and then make that the value of the macro variable &YR17. Then you get the SAS code using the numeric version of YR17 which I shall call YR17n which reads yr17n/4618 and everyone is happy.

Which brings us to the big question ... why use a macro variable here anyway? Since yr17 is obviously a character variable, this should work without macro variables.

 proc sql;
    select a.Group format=Group_num., a.Value, a.yr17,
          input(a.yr17,comma8.0)/input(b.yr17,comma8.0) as Pct17
    from T10_1(where=(Group=2)) as a left join t10_1(where=(group=1)) as b;
quit;