How to get preceding month or following month value

[CynthiaWei]

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,

C

[ChrisNZ]

MONTH and MONTH_PRECEDING have the same value for some rows?

[CynthiaWei]

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,

[andreas_lds]

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

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

[Kurt_Bremser · Accepted solution]

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.

[CynthiaWei]

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,