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.
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;
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
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.
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;
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!
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.
Ready to level-up your skills? Choose your own adventure.