topic Re: Interpolating Values from existing data set and create new data set in New SAS User

Interpolating Values from existing data set and create new data set

ezboral — Mon, 19 Aug 2019 15:33:25 GMT

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.

Re: Interpolating Values from existing data set and create new data set

Reeza — Mon, 19 Aug 2019 15:46:15 GMT

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

Re: Interpolating Values from existing data set and create new data set

ezboral — Mon, 19 Aug 2019 15:51:59 GMT

I have SAS Enterprise Guide

Re: Interpolating Values from existing data set and create new data set

Reeza — Mon, 19 Aug 2019 16:03:56 GMT

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.

Re: Interpolating Values from existing data set and create new data set

novinosrin — Mon, 19 Aug 2019 17:41:15 GMT

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;

Re: Interpolating Values from existing data set and create new data set

ballardw — Tue, 20 Aug 2019 17:17:58 GMT

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?

Re: Interpolating Values from existing data set and create new data set

ezboral — Tue, 20 Aug 2019 18:14:50 GMT

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

Re: Interpolating Values from existing data set and create new data set

ezboral — Thu, 22 Aug 2019 19:32:32 GMT

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

Re: Interpolating Values from existing data set and create new data set

Reeza — Thu, 22 Aug 2019 19:40:56 GMT

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.

Re: Interpolating Values from existing data set and create new data set

ezboral — Thu, 22 Aug 2019 19:42:58 GMT

Yes, I do have SAS ETS.

Re: Interpolating Values from existing data set and create new data set

novinosrin — Thu, 22 Aug 2019 20:00:28 GMT

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

Re: Interpolating Values from existing data set and create new data set

Reeza — Thu, 22 Aug 2019 20:11:33 GMT

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.

Re: Interpolating Values from existing data set and create new data set

Astounding — Thu, 22 Aug 2019 21:23:58 GMT

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;

Re: Interpolating Values from existing data set and create new data set

ezboral — Fri, 23 Aug 2019 20:26:11 GMT

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?

Re: Interpolating Values from existing data set and create new data set

Reeza — Fri, 23 Aug 2019 20:52:53 GMT

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?