Desktop productivity for business analysts and programmers

How to convert/expand quarter to date

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

How to convert/expand quarter to date

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.


Accepted Solutions
Solution
‎01-09-2017 04:56 PM
Respected Advisor
Posts: 4,132

Re: How to convert/expand quarter to date

[ Edited ]

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

View solution in original post


All Replies
PROC Star
Posts: 1,146

Re: How to convert/expand quarter to date

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

Frequent Contributor
Posts: 80

Re: How to convert/expand quarter to date

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.

Solution
‎01-09-2017 04:56 PM
Respected Advisor
Posts: 4,132

Re: How to convert/expand quarter to date

[ Edited ]

@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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 405 views
  • 2 likes
  • 3 in conversation