Computing new column

[gliwilson77]

Hi

I am a novice at programming and I have a year/month column with dates from 201604 to 202003.  I would like to create a new column where the 1st 3 months will show as 1m, 2nd 3 months will be 2m and so on therefore I would eventually have a new column with data ranging from 1m to 16m.

Eventually I would like to have it automated so that I can just alter the date range without having to manually input each indivdual date.

Year_Month  _3m_

201604           1m

201605           1m

201606           1m

201607           2m

201608           2m

201609           2m

201610           3m

201611           3m

201612           3m

I have tried to using arrays and macros but rather unsuccessfully  So any help will be greatly appreciated.

 G

[japelin · Accepted solution]

Is year_month variable numeric ?

In this code, the reference year is taken from the first observation.

data have;
input Year_Month;
datalines;
201604 
201605 
201606 
201607 
201608 
201609 
201610 
201611 
201612
; 
run;

data want;
  set have;
  retain basey;
  if _n_=1 then basey=input(put(year_month,6.),yymmn6.);
  month=cats(int(intck('month',basey,input(put(year_month,6.),yymmn6.))/3)+1,'m');
run;

[gliwilson77]

Thank you, I have just tried it but get an error

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space

             between a quoted string and the succeeding identifier is recommended.

[gliwilson77]

That code worked but only for the first 9 months.  For the full period (201604-202003) I should have 1m - 16m?

Thxs

[japelin]

I'm sorry.
I fixed it, so please test it.

[gliwilson77]

Brilliant! Thats done the trick.

Thank you so much.

[Kurt_Bremser]

Always store dates or date-related values as SAS date values, as this allows you to make use of functions and formats:

data have;
input ym :$6.;
datalines;
201604
201605
201606
201607
201608
201609
201610
201611
201612
;

data have_better;
set have;
year_month = input(ym,yymmn6.);
format year_month yymmn6.;
drop ym;
run;

data want;
set have_better;
retain start;
if _n_ = 1 then start = year_month;
_3m_ = cats(intck('quarter',start,year_month)+1,"m");
drop start;
run;

[andreas_lds]

/* untested */
data want;
  set have;
  
  ThreeMonth = cats(mod(_N_, 3) +1, 'm');
run;