Numeric to character conversion with substr and eval function

[David_Billa]

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

1

2

3

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;

[PaigeMiller]

---

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

[David_Billa]

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.

[Kurt_Bremser]

---

@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?

[David_Billa]

I will execute the program at any time.

[Kurt_Bremser]

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

[David_Billa]

How to tackle this?

[Kurt_Bremser]

---

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

[David_Billa]

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

[Tom]

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

[David_Billa]

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

[Kurt_Bremser]

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.

[Kurt_Bremser]

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

[David_Billa]

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

001

002

003

[FreelanceReinh]

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.