How to convert/expand quarter to date

[ayin]

Software version: SAS Enterprise Guide 7.1 (64-bit)

Questions:

from dataset_A to dataset_B

dataset_A :

TYPE QUARTER

A        2007Q1

A        2007Q2

A        2008Q3

...

B        2007Q1

B        2007Q2

B        2008Q3

...

dataset_B:    

TYPE QUARTER    DATE

A        2007Q1        1/1/2007

A        2007Q1        1/2/2007

...

A        2007Q1        1/31/2007

A        2007Q1        2/1/2007

...

A        2007Q2        4/1/2007

A        2007Q2        4/2/2007

...

B        2007Q1        1/1/2007

B        2007Q1        1/2/2007

...

B        2007Q1        1/31/2007

B        2007Q1        2/1/2007

...

B        2007Q2        4/1/2007

B        2007Q2        4/2/2007

...

Tried the following code:

proc sort data=work.testing;
by TYPE;
run;

proc expand data=work.testing out=work.testing2
from=qtr to=month;
by TYPE;
id QUARTER;
run;

However it didn't give a right result, and it has something to do with the new variable DATE.

Thanks.

[TomKari]

Typically one uses PROC EXPAND to "create" intevening records in a time series. These variables are specified in a CONVERT statement. Are you actually trying to do this, or just get the months that correspond to the quarters?

Tom

[ayin]

Thanks Tom for the quick response.

As we couldn't figure out a simple way to do that, my collegue used excel to generate a table that lists all dates and its corresponding quarter, and then she did a left join in SAS.

[Patrick · Accepted solution]

@ayin

I'd like to give "dislike" to this solution 😉

This is really not hard to do with SAS only. See below:

data have;
  input TYPE $ QUARTER:yyq6.;
  format quarter yyq6.;
  datalines;
A 2007Q1
A 2007Q2
A 2008Q3
B 2007Q1
B 2007Q2
B 2008Q3
;
run;

data want;
  set have;
  format date date9.;
  do date=quarter to intnx('quarter',quarter,0,'e');
    output;
  end;
run;