problem with interpolating quarterly data into monthly data

[MaxiHösi]

Hi SAS-Forum-Members,

I have a problem with interpolating quarterly data into monthly data.

I have a data that looks like that:

Car_Brand	Year	Sales_Q1	Sales_Q2	Sales_Q3	Sales_Q4
Hyundai	2018	100	130	115	100
BMW	2018	100	130	115	100
Tesla	2018	120	150	135	120
...	...	...	...	...	...
Hyundai	2019	200	260	305	290
BMW	2019	220	280	325	310
...	...	...	...	...	...
Hyundai	2020	300	600	570	600
BMW	2020	320	620	590	620
...	...	...	...	...	...

As you can see there are not always the same Car Brands in every year. The Car Brands can be different every year e.g. when a car_brand is insolvent (usually its the same but in the example Tesla has no sales in 2019, 2020 and so on).

My aim is to interpolate the quarterly data into monthly data. For the first quarter it should look like this:

Car_Brand	Year	Sales_Jan	Sales_Feb	Sales_Mar	Sales_Apr
Hyundai	2019	133,333	166,666	200	...
BMW	2019	140	180	220	...
...	...	...	...	...	...
Hyundai	2020	293,333	296,666	300	...
BMW	2020	313,333	316,666	320	...
...	...	...	...	...	...

I need the value of the 4th quarter of 2019 to interpolate the values of January, February and March 2020 and that caused me great difficulties

I hope you understand what my problem is and can help me.

Thanks a lot and have a nice day.

[LinusH]

So you need the previous quarter sales to create a curve for the next quarters months?

But I don't understand what interpollation means here. Aren't the sales figures a sum of sales for each quarter? Wouldn't you then divide each month by three (after creating your curve)...?

Or does month mean rolling 3 months sales? Then it makes sense.

Also, didn't understand how you want to manage brands that doesn't exist certain periods. In you result table you have removed Tesla.

First of all, transpose your data so you have one row per brand, year and quarter (this should always be the default structure when storing data).

Sort by brand, year and quarter.

You can then use RETAIN in a data step to keep the previous quarters sales when looking at the next record.

You can keep track on that you are working with the same brand by using BY statement, and use first. and last. logic to assign and calculating values.

Pseudo code for last step, untested:

data want;
   set have;
   by brand;
   retain prev_qsales;
   if not first.brand then do;
      do m=1 to 3;
         /* your calculation logic goes here */
         month = (quarter - 1) * 3  + m;
         output;
      end;
   end;
   if not last.brand prev_qsales = sales; 
run;

[Ksharp]

That would be a lot of easy if you have SAS/ETS by using PROC EXPAND.

data have;
infile cards expandtabs truncover;
input Car_Brand	$ Year 	Sales_Q1	Sales_Q2	Sales_Q3	Sales_Q4;
cards;
Hyundai	2018 100	130	115	100
BMW	2018	100	130	115	100
Tesla	2018	120	150	135	120
Hyundai	2019	200	260	305	290
BMW	 2019	220	280	325	310
Hyundai	2020 300	600	570	600
BMW	2020	320	620	590	620
;
proc sort data=have out=temp;
by Car_Brand Year;
run;
proc transpose data=temp out=temp2;
by Car_Brand Year;
var Sales_:;
run;
data temp3;
 set temp2; 
 yyq=input(cats(year,scan(_name_,-1,'_')),yyq6.);
 format yyq yymmdd10.;
run;
data temp4;
 merge temp3 temp3(keep=Car_Brand yyq rename=(Car_Brand=_Car_Brand yyq=_yyq) firstobs=2);
 output;
 if Car_Brand=_Car_Brand then do;
   do i=1 to intck('month',yyq,_yyq)-1;
    yyq=intnx('month',yyq,1);
	call missing(col1);
	output;
   end;
 end;
 keep Car_Brand col1 yyq;
run;

proc expand data=temp4 out=want method=join;
by Car_Brand;
convert col1;
id yyq;
run;

[MaxiHösi]

Thanks a lot. This code has already helped me a lot. It doesn't quite work, because not always a third is taken (I think that's due to the different days), but it was a good starting point. I don't have much time today, but maybe I can post the code on the weekend like I did. I still have some problems with retain and solved it very cumbersomely.