turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Max price change within interval i.e. 30 days or 3...

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-03-2017 12:08 PM

Using teh dataset below, is there a straightforward way of calculating:

(1) the largest price increase in any consecutive 30/360/720 day period. So the algorithm would need to check the price change of from every day to every other day within 30/360/720 days and output a maximum

(2) the largest price increase in a particular month

(3) the largest price increase in a particular year

(4) the correlation of price movements between January 2015 and January 2016 i.e. are price movements in Jan 2015 correlated with price movements in Jan 2016?

data have;

attrib date length=4 format=date9.;

do date = '1jan2015'd to '31dec2016'd;

price = int(ranuni(225465114) * 30) + 95;

output;

end;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

04-03-2017 12:47 PM

I think you may need to expand on your definitions of 2 and 3.

2) the largest price increase in a particular month: Do you mean single day to day increase within calendar month, largest end of month compared with first of month, increase from previous month end, or something else.

Some of this may be easier if you have SAS/ETS available. Do you?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

04-03-2017 12:52 PM

Hi ballard, for part 2) I mean largest price movement within a month .i.e. Could be between the 14th and 15th or the 1st and the 30th or any combination

3) same as above but it's a calendar year instead of a month

3) same as above but it's a calendar year instead of a month

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

04-03-2017 12:53 PM

I don't have sas/ets

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

04-03-2017 03:50 PM

Any suggestions on an approach?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

04-03-2017 05:00 PM

```
I assumed disjoint months. not sure what you want for output.
proc sql;
create
table cart as
select
l.month
,max(abs(l.price - r.price)) as jandif
from
date as l full outer join date as r
on
l.month ne r.month and
l.date ne r.date
group
by l.month
;quit;
Up to 40 obs WORK.CART total obs=3
Obs MONTH JANDIF
1 1 29
2 2 28
3 3 29
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

04-03-2017 05:41 PM

How about providing a concrete (NOT random) 45 days of data and show us what the result for the 30 day problem would look like based on that data. That should not be to large to do by hand.

You may also need to be careful about "increase". What is the result for if the data only decreases over a given time interval?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

04-04-2017 07:48 AM - edited 04-04-2017 08:04 AM

Hi Ballardw

Thanks for your feedback.

I've attached an excel with two tabs:

The first shows the calculation in excel for calculating the max price increase (not decrease) in a calendar week (Mon-Sun) over a two month period.

The second is an expansion of the first example and shows how to calculate the max price increase in any 7 day consecutive period.

Once the above has been solved, it is straightforward expanding this to months/ bigger intervals.

You will see in both examples that the agorithm needs to calculate a matrix of price change and then max over the matrix

So to calculate the max price change in a calendar week you need to perform 21 calculations and take the max the get the max price change. If we wanted to do the same for January, you would need to perform 1,860 calculations.

Does this make sense?

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

04-04-2017 11:16 AM

I think the solution is probably through the use of arrays but they're not my strongpoint

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

04-04-2017 01:26 PM

Any advice??

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

04-04-2017 05:37 PM

See if this does the 7 days. Since the data is random it may not match expectations.

data have; attrib date length=4 format=date9.; do date = '1jan2015'd to '31dec2016'd; price = int(ranuni(225465114) * 30) + 95; PriceInc = dif(Price)/lag(price); If PriceInc<0 then PriceInc=.; Max7Day = max(priceInc,lag(priceinc),lag2(priceinc),lag3(priceinc),lag4(priceinc),lag5(priceinc),lag6(priceinc)); output; end; label PriceInc = "Percent increase from previous day" Max7Day = "Maximum daily price increase percent 7 days" ; format PriceInc Max7Day percent8.1; run;

If that at matches then some of what you requested may be possible with other report procedures and careful use of formats

proc tabulate data=have; class date; format date yymon.; var priceinc; tables date, priceinc*max*f=percent8.1; run;