Date format

[Morganna]

Hi, I'm trying to get data from 3 months back using the code proc sql ;create table as test_data
Select sum *
From my_data where date = put('month', & mon1_yyyymm., - 3),yymmn6.) but I don't get any output, so when I check the 3 months back date it appears like this '251311'

[Tom]

Your code does not make any sense. Did you leave out part of it?

What TYPE of variable is DATE?  Is it Numeric or Character?  If Character what is its LENGTH?  If it is Numeric what FORMAT does it have attached to it?

What string of characters does the macro variable mon1_yyyymm have in it? (everything is a string of characters to the macro processor)

If DATE is a numeric variable with date values that is being formatted with the YYMMN6. format so it looks like YYYYMM value (202403 for example) then you need to compare it to a DATE value. (and you also need to know what day of the month the values are using, since the YYMMN format does not display that part of the data.

IF MON1_YYYYMM have a string of digits in the style YYYYMM, like 202403, then to treat it as a DATE value you need to first convert the string into a date value.

Let's assume that MON1_YYYYMM has strings in that style.

So assuming that DATE is a numeric variable with DATE values and that dates are stored as the first day of the month then you could find the values that are three months before 202403 with code like:

where date = intnx('month',input("&mon1_yyyymm.01",yymmdd8.),-3) 

Which says (working from innermost) append digits 01 to the macro variable MON1_YYYYMM to get an 8 digit string, convert that to a date value. Then use INTNX() with the MONTH interval to  calculate the beginning of the month that is three months before that date and test if DATE matches that date value.

Now if DATE instead has strings in the same style as the macro variable then you need wrap that INTNX() call inside a PUT() function call.

date = put(intnx(......),yymmn6.)

And if DATE is numeric, but does not have date values, instead it just has numbers in the style of YYY,YMM , for example the number 202,403 for the current month of March 2024. then wrap that PUT() inside an INPUT() that will convert the generated 6 digit string back into a number.

date = input(put(intnx(......),yymmn6.),6.)