topic Re: How to join 2020 data to 2021 for date format YYYYMM in SAS Programming

How to join 2020 data to 2021 for date format YYYYMM

adisal — Thu, 07 Jan 2021 21:58:21 GMT

Hello ,

I have a scenario where I join the data from pervious month to the current month. The format for the column is YYYYMM (202001). The condition is like below:

input(YYYMM,6.)+1 = input(put(DATEPART(DATE),yymmn6.),6.)

I am unable to figure out how to make this condition work for 2021 . I have to fetch data where I join Dec 2020 data to Jan 2021,

so I want the condition like this 202012 +1 = 202101 but the above condition will not fetch 202101 data but instead give 202012 +1 = 201213.

Thanks for helping .

Re: How to join 2020 data to 2021 for date format YYYYMM

Reeza — Thu, 07 Jan 2021 22:32:07 GMT

year(t1.date) = year(datepart(t2.date)) + 1 and month(t1,date) = month(t2.date)

This approach checks the years match and then the months

This approach uses INTNX() and compares the actual dates, making sure to align them all to the start of the month.

intnx('month', t1.date, 0, 'b') = intnx('month', datepart(t2.date), -12, 'b')

Note that if the day is present it will be included in the comparison which is why I've used the alignment parameter in the INTNX function to align the date to the beginning of the month.

If neither of these work, please provide some examples of what isn't working for you.

@adisal wrote:

Hello ,

I have a scenario where I join the data from pervious month to the current month. The format for the column is YYYYMM (202001). The condition is like below:

input(YYYMM,6.)+1 = input(put(DATEPART(DATE),yymmn6.),6.)

I am unable to figure out how to make this condition work for 2021 . I have to fetch data where I join Dec 2020 data to Jan 2021,

so I want the condition like this 202012 +1 = 202101 but the above condition will not fetch 202101 data but instead give 202012 +1 = 201213.

Thanks for helping .

Re: How to join 2020 data to 2021 for date format YYYYMM

PGStats — Thu, 07 Jan 2021 22:38:00 GMT

if dt2020 is a number like 202001 and DATE is a SAS datetime value, the condition for matching consecutive months should be:

intnx("month", mdy(mod(dt2020,100),1,int(dt2020/100)), 1) = intnx("month", datepart(date), 0)

Re: How to join 2020 data to 2021 for date format YYYYMM

ballardw — Thu, 07 Jan 2021 22:40:26 GMT

It really isn't clear what you want.

To manipulate date values, like add a month, you would use the INTNX function.

If your starting value is a datetime then

Newvalue = intnx('dtmonth', datetimevariable, 1); would advance a datetime value by one month.

If you only want a date

Newvalue = intnx('month', datepart(datetimevariable),1);

you can add an alignment to the intnx function to be the Beginning, End, or Same (interval int the result)

Newvalue = intnx('month', datepart(datetimevariable),1,'B'); would force the result to be the first day of the next month.

Then assign a format if creating a new variable.

There NEVER a need to use input(put(DATEPART(DATE),<format>),<informat>);

If you want a date just use: Newvar = datepart(date); And assign a format to the result.