Hello,
I have data that is at a quarterly frequency and I would like to convert it into a monthly frequency, filling in the gaps simply by repeating the observed quarterly value over three months. I thought proc expand could do the trick, but I can not find options that fit what I am trying to do. Any suggestions would be greatly appreciated.
HAVE
ID | Year | Quarter | XYZ |
---|---|---|---|
1 | 2005 | 1 | 100 |
1 | 2005 | 2 | 175 |
1 | 2005 | 3 | 216 |
1 | 2005 | 4 | 257 |
1 | 2006 | 1 | 304 |
1 | 2006 | 2 | 345 |
1 | 2006 | 3 | 347 |
1 | 2006 | 4 | 456 |
2 | 2005 | 1 | 235 |
2 | 2005 | 2 | 210 |
2 | 2005 | 3 | 277 |
2 | 2005 | 4 | 400 |
WANT
ID | YEAR | QUARTER | MONTH | XYZ |
---|---|---|---|---|
1 | 2005 | 1 | 1 | 100 |
1 | 2005 | 1 | 2 | 100 |
1 | 2005 | 1 | 3 | 100 |
1 | 2005 | 2 | 4 | 175 |
1 | 2005 | 2 | 5 | 175 |
1 | 2005 | 2 | 6 | 175 |
1 | 2005 | 3 | 7 | 216 |
1 | 2005 | 3 | 8 | 216 |
1 | 2005 | 3 | 9 | 216 |
1 | 2005 | 4 | 10 | 257 |
1 | 2005 | 4 | 11 | 257 |
1 | 2005 | 4 | 12 | 257 |
1 | 2006 | 1 | 1 | 304 |
1 | 2006 | 1 | 2 | 304 |
1 | 2006 | 1 | 3 | 304 |
1 | 2006 | 2 | 4 | 345 |
Please try , First create the dummy dataset with quarter and month and use sql for joining
data dummy;
Quarter= 1 ;
do month = 1 to 3;
output;
end;
Quarter= 2 ;
do month = 4 to 6;
output;
end;
Quarter= 3 ;
do month = 7 to 9;
output;
end;
Quarter= 4 ;
do month = 10 to 12;
output;
end;
run;
proc sql;
create table want as select a.*,b.month from have as a left join dummy as b on a.quarter=b.quarter
order by a.id, a.year, a.quarter,b.month;
quit;
Thanks,
Jag
Please try , First create the dummy dataset with quarter and month and use sql for joining
data dummy;
Quarter= 1 ;
do month = 1 to 3;
output;
end;
Quarter= 2 ;
do month = 4 to 6;
output;
end;
Quarter= 3 ;
do month = 7 to 9;
output;
end;
Quarter= 4 ;
do month = 10 to 12;
output;
end;
run;
proc sql;
create table want as select a.*,b.month from have as a left join dummy as b on a.quarter=b.quarter
order by a.id, a.year, a.quarter,b.month;
quit;
Thanks,
Jag
CODE NOT TESTED.
data want;
set have;
if Quarter= 1 then do;
do month = 1 to 3;
output;
end;
end;
if Quarter= 2 then do;
do month = 4 to 6;
output;
end;
end;
if Quarter= 3 then do;
do month = 7 to 9;
output;
end;
end;
if Quarter= 4 then do;
do month = 10 to 12;
output;
end;
end;
run;
This did exactly what I needed. Thank you so much!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.