BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
adamhu
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

3 REPLIES 3
Jagadishkatam
Amethyst | Level 16

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
Ksharp
Super User

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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