topic Re: How to convert/expand quarter to date in SAS Enterprise Guide

How to convert/expand quarter to date

ayin — Mon, 09 Jan 2017 04:16:26 GMT

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.

Re: How to convert/expand quarter to date

TomKari — Mon, 09 Jan 2017 04:48:22 GMT

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

Re: How to convert/expand quarter to date

ayin — Mon, 09 Jan 2017 05:43:01 GMT

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.

Re: How to convert/expand quarter to date

Patrick — Mon, 09 Jan 2017 11:09:13 GMT

@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;