topic Re: Automatic linear calculation in SAS Software for Learning Community

Automatic linear calculation

ziv — Tue, 10 Jan 2023 14:15:19 GMT

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

120.....

until 360

10
30
30
60

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

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

Re: Automatic linear calculation

sbxkoenk — Wed, 11 Jan 2023 23:39:36 GMT

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

Re: Automatic linear calculation

ziv — Thu, 12 Jan 2023 09:14:55 GMT

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

Re: Automatic linear calculation

sbxkoenk — Thu, 12 Jan 2023 11:05:24 GMT

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

Re: Automatic linear calculation

ziv — Thu, 12 Jan 2023 12:17:55 GMT

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

Re: Automatic linear calculation

ziv — Thu, 12 Jan 2023 12:20:31 GMT

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

thanks evryone ;

Re: Automatic linear calculation

sbxkoenk — Thu, 12 Jan 2023 14:08:06 GMT

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

Re: Automatic linear calculation

sbxkoenk — Thu, 12 Jan 2023 14:11:35 GMT

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

Re: Automatic linear calculation

ziv — Thu, 12 Jan 2023 15:55:36 GMT

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

thank you very very 🙂

Re: Automatic linear calculation

sbxkoenk — Thu, 12 Jan 2023 19:19:44 GMT

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

Re: Automatic linear calculation

ziv — Sun, 15 Jan 2023 07:02:13 GMT

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.

Re: Automatic linear calculation

sbxkoenk — Sun, 15 Jan 2023 21:16:16 GMT

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