Converting quarterly data to monthly

[adamhu]

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

[Jagadishkatam · Accepted solution]

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

[Ksharp]

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;

[adamhu]

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