BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ayin
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

3 REPLIES 3
TomKari
Onyx | Level 15

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
Quartz | Level 8

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
Opal | Level 21

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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