topic Re: Numeric to character conversion with substr and eval function in SAS Programming

Numeric to character conversion with substr and eval function

David_Billa — Fri, 29 Jan 2021 11:55:20 GMT

I've a variable called 'mon_qtr' which was numeric and it had values like,

and now this variable has been converted to character and it has values like,

001

002

003

When this variable was numeric, I used the below condition in where clause. As this variable has been converted to character now, I would like to know how to tweak this condition.

mon_qtr in (%substr(&Reportingdate1,5,2), %eval(%substr(&Reportingdate1,5,2)-1),%eval(%substr(&Reportingdate1,5,2)-2))

Value of the macro variable 'Reportingdate1' will be like,

%let Reportingdate1=20200331;

Re: Numeric to character conversion with substr and eval function

PaigeMiller — Fri, 29 Jan 2021 12:05:41 GMT

@David_Billa wrote:

When this variable was numeric, I used the below condition in where clause. As this variable has been converted to character now, I would like to know how to tweak this condition.
mon_qtr in (%substr(&Reportingdate1,5,2), %eval(%substr(&Reportingdate1,5,2)-1),%eval(%substr(&Reportingdate1,5,2)-2))

Don't convert it to character (or keep the numeric variable) and you now have working code.

More importantly, don't work with dates as character strings, SAS has built in many functions that make your life easier if you handle them as actual dates (which are numeric). As an example, when the month part of &reportingdate is 01, the second %eval gives you a month of 0, and the third %eval gives you a month of -1, is that what you want? Again, using SAS dates as actual numbers rather than characters, and using SAS date functions, avoids all of these problems, and when the month of &reportingdate is 01, these SAS functions know that the previous month was 12 and the month before that is 11.

Re: Numeric to character conversion with substr and eval function

Kurt_Bremser — Fri, 29 Jan 2021 12:33:32 GMT

mon_qtr should be a complete date value, and the macro variable a raw date (Maxim 28). Then your condition is

intnx('month',&reportingdate1.,-2,'b') le mon_qtr le intnx('month',&reportingdate1.,0,'e')

and it will work seamlessly across year boundaries.

Make your data intelligent (Maxim 33).

Re: Numeric to character conversion with substr and eval function

David_Billa — Fri, 29 Jan 2021 13:12:16 GMT

@Kurt_Bremser mon_qtr is character data type with the values like,

001

002

003

Re: Numeric to character conversion with substr and eval function

David_Billa — Fri, 29 Jan 2021 13:52:44 GMT

As per Business needs the variable mon_qtr has been already changed to character. Now I want to see how can I update the condition in where clause.

Re: Numeric to character conversion with substr and eval function

FreelanceReinh — Fri, 29 Jan 2021 14:09:03 GMT

Just replace mon_qtr by

input(mon_qtr, 32.)

in your WHERE condition. Then you're back to numeric "values like 1, 2, 3" and the rest of the code works as before.

Re: Numeric to character conversion with substr and eval function

Kurt_Bremser — Fri, 29 Jan 2021 15:31:22 GMT

@David_Billa wrote:

As per Business needs the variable mon_qtr has been already changed to character. Now I want to see how can I update the condition in where clause.

So you never run that program in January or February?

Re: Numeric to character conversion with substr and eval function

David_Billa — Fri, 29 Jan 2021 15:34:13 GMT

I will execute the program at any time.

Re: Numeric to character conversion with substr and eval function

Kurt_Bremser — Fri, 29 Jan 2021 15:36:36 GMT

Then it will fail in those two months, as it will calculate target months of 0 and -1, as already mentioned.

Re: Numeric to character conversion with substr and eval function

David_Billa — Fri, 29 Jan 2021 15:58:13 GMT

How to tackle this?

Re: Numeric to character conversion with substr and eval function

Tom — Fri, 29 Jan 2021 15:59:10 GMT

You did not provide enough examples of MON_QTR values to understand if it is supposed to represent MONTH or QUARTER. The values 1 to 3 could be valid for either.

Also you did not show any examples of non-positive values of MON_QTR, but your %EVAL() calculations could generate such values. Does that mean when the month part of Reportingdate is January that you only want to use data from January? But when it is March you want to use data from January, February and March?

It looks like from your code that you expect MON_QTR to have values from -1 to 12.

Note that you can use the INPUT() function to convert values like '001' into the number 1.

Re: Numeric to character conversion with substr and eval function

David_Billa — Fri, 29 Jan 2021 16:03:13 GMT

Mon_Qtr value represent the values of months and it always positive.

Value of reporting date will always be any one of quarter ending date.

Re: Numeric to character conversion with substr and eval function

Kurt_Bremser — Fri, 29 Jan 2021 16:03:42 GMT

@David_Billa wrote:
How to tackle this?

Maxim 33: Intelligent Data Makes for Intelligent Programs.

By storing dates (and date-related values like quarters) as SAS dates, you enable yourself to make use of the plethora of tools that SAS provides for date values.

Re: Numeric to character conversion with substr and eval function

David_Billa — Fri, 29 Jan 2021 16:06:13 GMT

I'm sorry if I was mistaken.

Values of the macro variable will be any one of the quarter ending date. So
it will run only for the months March, June,September and December

Re: Numeric to character conversion with substr and eval function

Tom — Fri, 29 Jan 2021 16:49:11 GMT

So if the only input months are 3,6,9, and 12 then you current code will work. All you need to do is modify it so the IN operator is comparing values with the same type.

So either convert MON_QTR to a number.

%let Reporting_Month=%substr(&Reportingdate1,5,2);
...
input(mon_qtr,32.) in (&reporting_month %eval(&reporting_month-1) %eval(&reporting_month-2))
...

or compare it to character strings instead of numbers.

mon_qtr in 
("%sysfunc(putn(&reporting_month,Z3.))"
 "%sysfunc(putn(&reporting_month-1,Z3.))"
 "%sysfunc(putn(&reporting_month-2,Z3.))"
)

Re: Numeric to character conversion with substr and eval function

David_Billa — Mon, 01 Feb 2021 08:00:32 GMT

@Tom I got the error when I tried to implement the recommended solution.

Log:

1172                mon_qtr in
1173                ("%sysfunc(putn(%substr(&Reportingdate1,5,2),Z3.))"
SYMBOLGEN:  Macro variable REPORTINGDATE1 resolves to 20200331
1174                 "%sysfunc(putn(%eval(%substr(&Reportingdate1,5,2)-1),Z3.))"
SYMBOLGEN:  Macro variable REPORTINGDATE1 resolves to 20200331
1175                 "%sysfunc(putn(%eval(%substr(&Reportingdate1,5,2)-2),Z3.))")  &
SYMBOLGEN:  Macro variable REPORTINGDATE1 resolves to 20200331
ERROR: Expression using IN has components that are of different data types.
NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC SQL WHERE clause optimization.

Re: Numeric to character conversion with substr and eval function

Kurt_Bremser — Mon, 01 Feb 2021 08:36:11 GMT

Seems your mon_qtr is numeric, but you compare it to character strings.

It is the old problem of not providing data in usable form (data step with datalines), which forces us to make guesses, and of not posting the whole code of a step, which would allow us to recreate your situation as is.