topic Re: How to get preceding month or following month value in SAS Programming

How to get preceding month or following month value

CynthiaWei — Wed, 31 Mar 2021 22:24:40 GMT

Hi SAS Pros,

I have a dataset with month values like this:

ID	month
1	2015-02
1	2015-10
2	2015-10
2	2019-01
2	2019-02
3	2019-01
3	2019-02
3	2019-06
3	2019-07
3	2019-11

I want to create a new variable called month_preceding with the values for the following month showed as below:

	month	month_preceding
1	2015-02	2015-02
1		2015-03
1	2015-10	2015-10
1		2015-11
2	2015-10	2015-10
2		2015-11
2	2019-01	2019-01
2	2019-02	2019-02
2		2019-03
3	2019-01	2019-01
3	2019-02	2019-02
3		2019-03
3	2019-06	2019-06
3	2019-07	2019-07
3		2019-08
3	2019-11	2019-11
3		2019-12

If they are non-continous month, then just have that month and its following month for the each ID; if they are continuous months, then just have those continuous months and the following month of the last month.

Thank you very much for any help!

Best regards,

Re: How to get preceding month or following month value

ChrisNZ — Wed, 31 Mar 2021 22:53:26 GMT

MONTH and MONTH_PRECEDING have the same value for some rows?

Re: How to get preceding month or following month value

andreas_lds — Thu, 01 Apr 2021 06:28:41 GMT

Is "month" a formatted sas date or a string?

If it is not a date, fix that problem first.

Re: How to get preceding month or following month value

Kurt_Bremser — Thu, 01 Apr 2021 06:53:41 GMT

Use a "look-ahead":

data have;
input id $ monthstr :$7.;
format month yymmd7.;
month = input(monthstr!!"-01",yymmdd10.);
drop monthstr;
datalines;
1 2015-02
1 2015-10
2 2015-10
2 2019-01
2 2019-02
3 2019-01
3 2019-02
3 2019-06
3 2019-07
3 2019-11
;

data want;
merge
  have
  have (
    firstobs=2
    rename=(id=_id month=_month)
  )
;
format preceding_month yymmd7.;
preceding_month = month;
output;
if _id ne id or _month ne intnx('month',month,1,'s')
then do;
  month = .;
  preceding_month = intnx('month',preceding_month,1,'s');
  output;
end;
drop _:;
run;

proc print data=want noobs;
run;

Please do always supply example data in a data step with datalines! Otherwise your helpers will have to do extra work, and make guesses about variable attributes and the real content.

Even idiots like myself manage to do it, so it should not be a problem for you.

Re: How to get preceding month or following month value

CynthiaWei — Thu, 01 Apr 2021 17:33:02 GMT

Hi,

Thank you for getting back to me! Sorry for the confusion. Actually, month_preceding should be called month_new because it will still include the values in month and have one more preceding/following month. If the months in MONTH are not continuous (e.g. 2015-01 and 2019-03), then just have the month and following month for each observation in the MONTH_NEW (2015-01, 2015-02 and 2019-03, 2019-04). If the months in MONTH are continuous (e.g. 2016-04, 2016-05, & 2016-06), then want to have these three months and one following month of the last month (2016-06), which is 2016-07, in the MONTH_NEW (e.g. 2016-04, 2016-05, 2016-06, & 2016-07).

Thanks,

Re: How to get preceding month or following month value

CynthiaWei — Thu, 01 Apr 2021 23:45:07 GMT

Hi,

Thank you so much for the code! It worked good!

I am sorry that I was using a remote version of SAS, the little running man icon wasn't showing up. I will try to post SAS code for the data next time! Thanks for reminding me that!

Best regards,