topic PROC EXPAND in SAS Procedures

PROC EXPAND

vickyCh — Wed, 10 Jan 2018 04:14:20 GMT

How can I obtain the moving slope coefficients of regression analysis in a data series, like in PROC EXPAND convert /transformout=( MOVTVALUE n) or (MOVSTD n) ? There are thousands of sales data and we want to know the sales trend.

ENTITY DATE sales

aaa 2003/1/2 7864500

aaa 2003/1/3 5963400

aaa 2003/1/6 7923300

aaa 2003/1/7 11907000

aaa 2003/1/8 9509700

aaa 2003/1/9 10711600

aaa 2003/1/10 9961400

aaa 2003/1/13 10499100

aaa 2003/1/14 7569200

aaa 2003/1/15 8147600

aaa 2003/1/16 10112800

aaa 2003/1/17 17332600

aaa 2003/1/21 8927000

aaa 2003/1/22 9433100

aaa 2003/1/23 8371500

aaa 2003/1/24 7748000

aaa 2003/1/27 9307800

aaa 2003/1/28 8018400

aaa 2003/1/29 7757500

aaa 2003/1/30 7349500

aaa 2003/1/31 9744100

aaa 2003/2/3 6483500

aaa 2003/2/4 9216200

aaa 2003/2/5 7427500

aaa 2003/2/6 7755200

...

Re: PROC EXPAND

Reeza — Wed, 10 Jan 2018 04:36:14 GMT

Is this a continuously moving slope calculation? Or a specific window?

The functions available by PROC EXPAND are here: http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_expand_sect026.htm

I don't think there's directly a regression slope coefficient, so my default would be to use data step functions to manually calculate the slope at each line.

I'm also 99% sure that this has been asked and answered on here in the last 6 months.

Re: PROC EXPAND

vickyCh — Wed, 10 Jan 2018 05:13:53 GMT

It is a continuously moving slope calculation.

Since there are thousands of sales data, it may not be practical to manually calculate the slope at each line if, for example, we want to know each month (30 days) sales trend (slope coefficient) difference.

Re: PROC EXPAND

Reeza — Wed, 10 Jan 2018 05:27:49 GMT

The number of lines wouldn’t matter to the calculation...

It’s not like you’d type out a formula for each line, just retain the components.

How are you handling time? I’m assuming that’s the x, or do you have another variable?

@vickyCh wrote:

It is a continuously moving slope calculation.

Since there are thousands of sales data, it may not be practical to manually calculate the slope at each line if, for example, we want to know each month (30 days) sales trend (slope coefficient) difference.

Re: PROC EXPAND

PGStats — Wed, 10 Jan 2018 05:37:58 GMT

Maybe you could start with:

data have;
input ENTITY $    DATE :yymmdd10.  sales :12.6;
format date yymmdd10. sales best7.2;
datalines;
aaa  2003/1/2 7864500
aaa  2003/1/3 5963400
aaa  2003/1/6 7923300
aaa  2003/1/7 11907000
aaa  2003/1/8 9509700
aaa  2003/1/9 10711600
aaa  2003/1/10       9961400
aaa  2003/1/13       10499100
aaa  2003/1/14       7569200
aaa  2003/1/15       8147600
aaa  2003/1/16       10112800
aaa  2003/1/17       17332600
aaa  2003/1/21       8927000
aaa  2003/1/22       9433100
aaa  2003/1/23       8371500
aaa  2003/1/24       7748000
aaa  2003/1/27       9307800
aaa  2003/1/28       8018400
aaa  2003/1/29       7757500
aaa  2003/1/30       7349500
aaa  2003/1/31       9744100
aaa  2003/2/3    6483500
aaa  2003/2/4     9216200
aaa  2003/2/5     7427500
aaa  2003/2/6     7755200
;

proc loess data=have plots(only)=fit;
model sales=date / direct smooth=0.8;
output out=want predicted=predSales;
run;

data wantSlope;
set want;
slope = dif(predSales) / dif(date);
run;

title "Sale Trends";
proc sgplot data=wantSlope;
scatter x=date y=sales;
series x=date y=predSales;
series x=date y=slope / y2axis;
run;

Re: PROC EXPAND

mkeintz — Wed, 10 Jan 2018 09:37:39 GMT

PROC EXPAND can be a useful tool for rolling slopes, but you have to create cross-products (to be summed) prior to proc expand, and calculate beta afterwards. Here's an example generating rolling 12-month slopes of closing prices in sashelp.stocks:

proc sort data=sashelp.stocks out=stocks;
  by stock date;
run;

data vneed /view=vneed;
  set stocks (keep=stock date close);
  X=intck('month','01jan1984'd,date);
  XY=X*close;
run;

proc expand data=vneed out=need2 (where=(n=12)) method=none;
  by stock;
  id date;
  convert close=n     / transformin=(*0)  transformout=(+1 movsum 12);
  convert X=sumXX     / transformout=(movuss 12);
  convert XY=sumXY    / transformout=(movsum 12);
  convert close=meanY / transformout=(movave 12);
  convert X=meanX     / transformout=(movave 12);
run;

data want;
  set need2;
  beta =   (sumXY - n*meanX*meanY) / (sumXX - n*meanX*meanX);
run;

It's relatively simple because you just want the slope. If you define y=Y-mean(Y) and x=X-mean(X), (i.e. "de-meaned" X and Y), then the beta coefficient (slope) of Y regressed on X is the ratio of the sum(x*y)/sum(x*x).

So no matrices needed, and the numerator and denominator above are functions of the (1) sum of products of X*Y, (2) sum of squared X, and (3) the means of X and Y.

Re: PROC EXPAND

vickyCh — Thu, 11 Jan 2018 03:45:20 GMT

I would like to say a big thank you to all of you for your help, especially mkeintz.
That is exactly what we want.