topic Re: Converting quarterly data to monthly in SAS Procedures

Converting quarterly data to monthly

adamhu — Sun, 14 Jun 2015 08:24:20 GMT

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

Re: Converting quarterly data to monthly

Jagadishkatam — Sun, 14 Jun 2015 13:30:04 GMT

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

Re: Converting quarterly data to monthly

Ksharp — Sun, 14 Jun 2015 13:46:07 GMT

CODE NOT TESTED.

data want;

set have;

if Quarter= 1 then do;

do month = 1 to 3;

output;

end;

if Quarter= 2 then do;

do month = 4 to 6;

output;

end;

if Quarter= 3 then do;

do month = 7 to 9;

output;

end;

if Quarter= 4 then do;

do month = 10 to 12;

output;

end;

run;

Re: Converting quarterly data to monthly

adamhu — Mon, 15 Jun 2015 00:38:10 GMT

This did exactly what I needed. Thank you so much!!