topic Re: Computing new column in SAS Programming

Computing new column

gliwilson77 — Tue, 30 Mar 2021 08:17:54 GMT

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.

Re: Computing new column

japelin — Tue, 30 Mar 2021 10:18:47 GMT

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;

Re: Computing new column

gliwilson77 — Tue, 30 Mar 2021 08:37:48 GMT

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.

Re: Computing new column

Kurt_Bremser — Tue, 30 Mar 2021 08:38:04 GMT

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;

Re: Computing new column

andreas_lds — Tue, 30 Mar 2021 09:06:11 GMT

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

Re: Computing new column

gliwilson77 — Tue, 30 Mar 2021 09:46:24 GMT

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

Thxs

Re: Computing new column

japelin — Tue, 30 Mar 2021 10:15:29 GMT

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

Re: Computing new column

gliwilson77 — Tue, 30 Mar 2021 10:21:34 GMT

Brilliant! Thats done the trick.

Thank you so much.