Interpolating Values from existing data set and create new data set

[ezboral]

I have the following table called QtrPriceData:

Region       Year      Qtr       Value

North          2001      1          100

North          2001      2          103

North          2001      3          106

I would like to take that data set  called MonthlyPriceData and create a new data set with monthly data with the following output:

Region       Year      Qtr    Month   Value

North          2001      1        1           100

North          2001      1        2           101

North          2001      1        3           102

North          2001      2        1           103

North          2001      2        2           104

North          2001      2        3           105

North          2001      3        1           106

Essentially, I'm taking a quarterly data set and interpolating monthly values, which are in orange.  This requires adding a monthly column, and then adding two rows for each quarter, and then finally calculating the monthly value (just taking the difference between each quarter and dividing by 3 and adding that value to the last period).

I appreciate any suggestions.

[Reeza]

Do you have a license for SAS ETS? Or are you using SAS UE? If so, PROC TIMESERIES can work for you.

data ibm;
	set sashelp.stocks;
	where stock='IBM';

	if month(date)=7 then
		delete;
run;

proc sort data=ibm;
	by date;
run;

/*2*/
proc timeseries data=ibm out=ibm_no_missing;
	id date interval=month start='01Aug1986'd end='01Dec2005'd;
	var open;
run;

https://gist.github.com/statgeek/07a3708dee1225ceb9d4aa75daab2c52

[ezboral]

I have SAS Enterprise Guide

[Reeza]

SAS ETS is a module, you may or may not have in EG.

You can check what you have licensed in SAS using:
proc setinit; run;

And what's installed on your system using:
proc product_status; run;

The output will be in the log. 

If SAS/ETS is listed, you have it. Or you can try just running the code in my initial post.

[novinosrin]

data have;
input Region  $     Year      Qtr       Value;
cards;
North          2001      1          100
North          2001      2          103
North          2001      3          106
;

data want;
 do until(last.year);
   set have;
   by region year;
   if not last.year then  do month=1 to 3;
   value=ifn(month=1,value,sum(value,1));
    output;
   end;
   else do;
   month=1;
   output;
 end;
end;
run;

  

[ballardw]

Do you ever have a gap in the year/qtr combinations within the data for a region?

Something like this:

Region       Year      Qtr       Value

North          2003      2          100

North          2004      1          103

Do you want to limit decimals for interpolated values for any reason?

[ezboral]

No gap in the year/qt combos.  No need to limit decimals either.

[ezboral]

So I've made some progress on this problem.

The following code helped me add a monthly column.

data Monthly;
set Quarterly;
if qtr = 1 then do;
	do month = 1 to 3;
	output;
	end;
end;
if qtr = 2 then do;
	do month = 4 to 6;
	output;
	end;
end;
if qtr = 3 then do;
	do month = 7 to 9;
	output;
	end;
end;
if qtr = 4 then do;
	do month = 10 to 12;
	output;
	end;
end;
run;

I have my table in the desired structure below, but the values are duplicates of the beginning quarterly value.  

Have

Region       Year      Qtr    Month   Value

North          2001      1        1           100

North          2001      1        2           100

North          2001      1        3           100

North          2001      2        1           103

North          2001      2        2           103

North          2001      2        3           103

North          2001      3        1           106

I want to take the difference between month 1 in a given year and month 1 in the prior year, divide the total by three, and add that amount to month 2, then month 3.

Example in the table below.

Want

Region       Year      Qtr    Month   Value

North          2001      1        1           100

North          2001      1        2           101

North          2001      1        3           102

North          2001      2        1           103

North          2001      2        2           104

North          2001      2        3           105

North          2001      3        1           106

[Reeza]

Did you ever figure out if you have SAS ETS? Proc TIMESERIES and EXPAND are designed to do this very easily though of course you can always hardcode it or do it manually.

[ezboral]

Yes, I do have SAS ETS.

[novinosrin]

Hello @ezboral   Nice that you do have ETS. Reeza can guide you.  May i ask whether you tried the code I offered ?

[Reeza]

Didn't get a ton of time to play with this, will check in again later.

*add missing quarters;
proc timeseries data=have out=want1;
id date interval=qtr start='01Jan2001'd end='31Dec2001'd;
var value;
run;

*adds missing months;
proc timeseries data=want1 out=want2;
id date interval=month start='01Jan2001'd end='31Dec2001'd;
var value;
run;

*interpolation;
proc expand data=want2 out=want3;
id date;
convert value = new_value / transformout = (seqadd(1));
run;

---

@ezboral wrote:

Yes, I do have SAS ETS.

---

[Astounding]

It's actually something new that you don't want month going from 1 to 3 each time.  Your new code has it going from 1 to 12.

Here's a way you can do this:

data monthly;
set quarterly end=done;
month = qtr * 3 - 2;
output;
if done=0;
nextrec = _n_ + 1;
set quarterly point=nextrec (keep=value rename=(value=next_value));
increment = (next_value - value) / 3;
do month = month + 1 to month + 2;
   value = value + increment;
   output;
end;
drop nextrec increment;
run;

This variation has month taking on values from 1 to 12.  If you really want it to go from 1 to 3 only, replace this statement:

month = qtr * 3 - 2;

Just use this instead:

month = 1;

[ezboral]

Thanks everyone, I really appreciate the help.  I've not been able to make any of the queries above work in the way I intended.  However, I have made progress.  I've managed to get to the following place.

Region       Year      Qtr    Month   Value  Date

North          2001      1        1           100      1/1/2001

North          2001      1        2           100      2/1/2001

North          2001      1        3           100      3/1/2001

North          2001      2        1           103      4/1/2001

North          2001      2        2           103      5/1/2001

North          2001      2        3           103      6/1/2001

North          2001      3        1           106      7/1/2001

If this was my starting point, my goal is simply to take value column and change the orange values to the equidistant value from month to month every quarter.

proc expand was mentioned as a possible solution.  Does anyone know how that might work?

[Reeza]

There was a proc expand in the code above, did you run and try that solution on your data? What exactly did you get?

---

@ezboral wrote:

Thanks everyone, I really appreciate the help.  I've not been able to make any of the queries above work in the way I intended.  However, I have made progress.  I've managed to get to the following place.

Region       Year      Qtr    Month   Value  Date

North          2001      1        1           100      1/1/2001

North          2001      1        2           100      2/1/2001

North          2001      1        3           100      3/1/2001

North          2001      2        1           103      4/1/2001

North          2001      2        2           103      5/1/2001

North          2001      2        3           103      6/1/2001

North          2001      3        1           106      7/1/2001

If this was my starting point, my goal is simply to take value column and change the orange values to the equidistant value from month to month every quarter.

proc expand was mentioned as a possible solution.  Does anyone know how that might work?

---