Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- PROC EXPAND

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 01-09-2018 10:58 PM
(1947 views)

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

...

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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...

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

PG

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

That is exactly what we want.

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.