Help using Base SAS procedures

Converting quarterly data to monthly

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Converting quarterly data to monthly

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

IDYearQuarterXYZ
120051100
120052175
120053216
120054257
120061304
120062345
120063347
120064456
220051235
220052210
220053277
220054400

WANT

IDYEARQUARTERMONTHXYZ
1200511100
1200512100
1200513100
1200524175
1200525175
1200526175
1200537216
1200538216
1200539216
12005410257
12005411257
12005412257
1200611304
1200612304
1200613304
1200624345

Accepted Solutions
Solution
‎06-14-2015 09:30 AM
Trusted Advisor
Posts: 1,137

Re: Converting quarterly data to monthly

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

Thanks,
Jag

View solution in original post


All Replies
Solution
‎06-14-2015 09:30 AM
Trusted Advisor
Posts: 1,137

Re: Converting quarterly data to monthly

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

Thanks,
Jag
Super User
Posts: 10,035

Re: Converting quarterly data to monthly

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;

New Contributor
Posts: 2

Re: Converting quarterly data to monthly

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 815 views
  • 4 likes
  • 3 in conversation