Automatic linear calculation

[ziv]

I have -
A list of months called "priod" from month 1 to month 360 in varying leaps - sometimes of three months, sometimes half a year, sometimes a year. At the same time there is a column called "b" and accordingly a product price for each such period. These two variables I mentioned are in a table called CHANGE_PERIOD.
Example:

priod
1
3
6
12
24
36

60

120.....

until 360

b

10
30
30
60

70

90

I want-
Create a new column called "New" for those 360 months, where in this column the price changes every month in a linear calculation of what the price would have been if the change had happened every month. Example of what I need:
period_new
1
2
3
4
5
6
7
8
9
10
until 360

b
10
20
30
30

30

.............. 360 prices

[sbxkoenk]

SAS/ETS User's Guide
The EXPAND Procedure
Interpolating Missing Values
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/etsug/etsug_expand_gettingstarted04.htm

Requesting Different Interpolation Methods
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/etsug/etsug_expand_gettingstarted05.htm

Svolba, Gerhard, 2021. Have a look at your TIMESERIES data from a bird's-eye view - Profile their missing value structure, SAS Communities

Svolba, Gerhard, 2021. Replace MISSING VALUES in TIMESERIES DATA using PROC EXPAND and PROC TIMESERIES, SAS Communities

Svolba, Gerhard, 2021. Using the TIMESERIES procedure to check the continuity of your timeseries data, SAS Communities

Svolba, Gerhard, 2021. The structure of MISSING VALUES in your data - get a clearer picture with the %MV_PROFILING macro, SAS Communities

BR,

Koen

[ziv]

Hi, first of all, thank you very much for your reply.
I did not find a place that explains how to fill in the gaps with a linear change.
In the examples you sent, I saw that they show how to return the previous figure, average, value 0, etc.
Thanks, Ziv

[sbxkoenk]

Is this what you want (linear interpolation with method=join in PROC EXPAND)?

data have;
datum='01OCT2022'd; value=0;    output;
datum='01JAN2023'd; value=1000; output;
run;

PROC EXPAND data=have out=want method=join to=month;
 id datum;
 convert value;
run;
/* end of program */

Koen

[ziv]

I need the missing values to be automatically linear, for example everything between 3 and 6 should be 0.91

[ziv]

I need the missing values to be automatically linear, for example everything between 1 and 12 should be 0.91 

 thanks evryone ;

[sbxkoenk]

You want this ??
I already know the answer , because I have just (5 seconds ago) seen your screenshot.
The answer is NO --> You do NOT want the below , but I am still publishing the program 😉 :

data have;
date='01JAN2022'd; value=1;  output;
date='01DEC2022'd; value=12; output;
run;

proc timedata data=have out=want
              print=(/*scalars*/ arrays);
              id date interval=month acc=t format=yymmdd.;
              vars value;
              outarrays  myvalue;
              *outscalars mystats;

	   do t = 1 to 1;
		myvalue[t] = value[t];
       end;
       do t = 2 to dim(value);
		myvalue[t] = (12-1)/10;
       end;

run;
QUIT;
/* end of program */

Koen

[sbxkoenk]

Screenshot made it clear to me.
What you want is the "LAST VALUE CARRIED FORWARD" missing value imputation.
You can look in this communities for LACF or "LAST VALUE CARRIED FORWARD" , you will find many hits.

For numeric variables , it's extremely easy.
Just use PROC EXPAND with method=step for missing value interpolation.

You need an example?

BR,

Koen

[ziv]

Actually yes, I would love an example.
I'm new with sas,

thank you very very 🙂

[sbxkoenk]

Here you are :

data work.class;
 set sashelp.class;
 datum = _N_;
 if mod(_N_,3) NE 0 then do; weight=.; height=.; end;
run;

PROC EXPAND data=work.class out=work.class_out 
            method=step EXTRAPOLATE from=day to=day;
 id datum;
 convert weight = weight_out ;
 convert height = height_out ;
run;
/* end of program */

Koen

[ziv]

Thanks, but that's still not what I meant.
In your command between 5-7 he will put the value that was before -5
I want him to apply 6, a linear increase/decrease in this case an increase.

[sbxkoenk]

Like this?

( sorry, but it's not clear to me what you want exactly )
     Previous program had method=step.
     Now I changed method=step into method=join.

data work.class;
 set sashelp.class;
 datum = _N_;
 if mod(_N_,3) NE 0 then do; weight=.; height=.; end;
run;

PROC EXPAND data=work.class out=work.class_out 
            method=join EXTRAPOLATE from=day to=day;
 id datum;
 convert weight = weight_out ;
 convert height = height_out ;
run;
/* end of program */

Koen