DATA Step, Macro, Functions and more

Create array of price change combinations

Accepted Solution Solved
Reply
Super Contributor
Posts: 259
Accepted Solution

Create array of price change combinations

 

I would like to write a program that calculates an array/matrix of price changes like the one below and then calculates the maximum ( which is 9% in the case below). The example below is 7 days but I would like the flexibility of calculate any amount of days. Also, the price change can only be for consecutive days (hence half the matrix below is blank). 

 

 

YearMonthWeekDayPrice  234567 
2015Jan11100 11.5%3.0%4.5%6.0%7.5%9.0%9.0%
2015Jan12101.5 2 1.5%3.0%4.4%5.9%7.4% 
2015Jan13103 3  1.5%2.9%4.4%5.8% 
2015Jan14104.5 4   1.4%2.9%4.3% 
2015Jan15106 5    1.4%2.8% 
2015Jan16107.5 6     1.4% 

Accepted Solutions
Solution
‎04-05-2017 12:18 PM
Super Contributor
Posts: 259

Re: Create array of price change combinations

Hi Astounding

 

When I run the code below:

 

data have;
infile datalines;
input Year Month $ Week Day Price;
datalines;
2015 Jan 1 1 98
2015 Jan 1 2 87
2015 Jan 1 3 86
2015 Jan 1 4 86
2015 Jan 1 5 86
2015 Jan 1 6 87
2015 Jan 1 7 88
;
run;

proc transpose data=have prefix=price_ out=want;
by year month week;
var price;
run;

data want;
set want;
array price_ {*};
do i=1 to dim(price) - 1;
do j=i+1 to dim(price);
max_change = max(max_change, price_{j} / price_{i} - 1);
end;
end;
run;

 

I get the error

 

 

2123 data want;
2124 set want;
2125 array price_ {7};
2126 do i=1 to dim(price) - 1;
ERROR: The DIM, LBOUND, and HBOUND functions require an array name for
the first argument.
2127 do j=i+1 to dim(price);
ERROR: The DIM, LBOUND, and HBOUND functions require an array name for
the first argument.
2128 max_change = max(max_change, price_{j} / price_{i} - 1);
2129 end;
2130 end;
2131 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete. When this step was
stopped there were 0 observations and 15 variables.
WARNING: Data set WORK.WANT was not replaced because this step was
stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

View solution in original post


All Replies
Super User
Super User
Posts: 7,421

Re: Create array of price change combinations

Post test data in the form of a datastep!!

 

If you have a normalised table, then simply lag() the value, then transpose up.  However I really don't see much point in having all the original rows there, something like:

data inter;
  set have;
  diff=price-lag(price);
run;

proc transpose data=inter out=want;
  by year month week;
  var diff;
  id day;
run;

This gets you the first row other than 7 and the 9.0% as its not explained anywhere where those come from or what they are.

Super Contributor
Posts: 259

Re: Create array of price change combinations

HI RW9

 

Apologies, here is the data step:

 

data have;
infile datalines;
input Year Month $ Week Day Price;
datalines;
2015 Jan 1 1 100
2015 Jan 1 2 101.5
2015 Jan 1 3 103
2015 Jan 1 4 104.5
2015 Jan 1 5 106
2015 Jan 1 6 107.5
2015 Jan 1 7 109
;

 

To explain the array the first number in the first row is (101.5/100-1=1.5%), the second number in the first row is (103/100-1=3%), ..., the 6th number in the first row is (109/100-1=9%); the second number in the second row is (103/101.5-1) etc.

 

So the matrix calculates every possible price change over the 7 days. There are 21 in total

 

 

Super User
Super User
Posts: 7,421

Re: Create array of price change combinations

Thanks.  Its not clear to me why you couldn't just take max(day)'s value and substract min(day)'s value form it to get the final output?  Anyways, this shows calculating each day, then transpose up and take the last element as total:

data have;
infile datalines;
input Year Month $ Week Day Price;
datalines;
2015 Jan 1 1 100
2015 Jan 1 2 101.5
2015 Jan 1 3 103
2015 Jan 1 4 104.5
2015 Jan 1 5 106
2015 Jan 1 6 107.5
2015 Jan 1 7 109
;
run;
data inter;
  set have;
  retain firstprice;
  by week;
  if first.week then firstprice=price;
  else diff=price-firstprice;
run;

proc transpose data=inter out=want;
  by year month week;
  var diff;
  id day;
run;
data want;
  set want (drop=_name_);
  array vals{*} _:;
  total=vals{dim(vals)};
run;
Super Contributor
Posts: 259

Re: Create array of price change combinations

Hi RW9, yes that would work but that woulnd't work if the max value occurred before the min value i.e. 

 

Mon100 
Tues150Max
Wed110 
Thur90Min
Friday100 

 

The max increase above would be 50% (150/100), not 150/90. 

Super Contributor
Posts: 259

Re: Create array of price change combinations

Hi RW9

 

I tried this code for a different set of numbers and it doesn't work. See below

 

data have;
infile datalines;
input Year Month $ Week Day Price;
datalines;
2015 Jan 1 1 98
2015 Jan 1 2 87
2015 Jan 1 3 86
2015 Jan 1 4 86
2015 Jan 1 5 86
2015 Jan 1 6 87
2015 Jan 1 7 88
;
run;


data inter;
set have;
retain firstprice;
by week;
if first.week then firstprice=price;
else diff=price-firstprice;
run;

proc transpose data=inter out=want;
by year month week;
var diff;
id day;
run;
data want;
set want (drop=_name_);
array vals{*} _:;
total=vals{dim(vals)};
run;

 

I've changed the price data. Yuu will see the largest price increase results from 86 to 88 or 2.3%.The increase is not restricted to a daily increase. It just the biggest increase over the period. 

 

 

 

Super User
Super User
Posts: 7,421

Re: Create array of price change combinations

Don't have much time now, this is where i got, you will need ot modify for your forumal and then grab max of row total:

data have;
infile datalines;
input Year Month $ Week Day Price;
datalines;
2015 Jan 1 1 98
2015 Jan 1 2 87
2015 Jan 1 3 86
2015 Jan 1 4 86
2015 Jan 1 5 86
2015 Jan 1 6 87
2015 Jan 1 7 88
;
run;
proc transpose data=have out=inter;
  by year month week;
  var price;
  id day;
run;
data want;
  merge have inter (drop=_name_);
  by year month week;
  array vals{*} of _:;
  do i=1 to day;
    vals{i}=.;
  end;
  do i=1 to dim(vals);
    /* Replace the vals{i}-price with your actual formula!! */
    vals{i}=ifn(vals{i} ne .,vals{i}-price,.);
  end;
  row_total=max(of vals{*});
run;
Super Contributor
Posts: 259

Re: Create array of price change combinations

Hi RW9

 

I've spent a bit of time on this and I still can't finish off the code you presented above. I'm not familiar with arrays so that is probably where the issue is. This is more complicated than I thought it would be. 

Super User
Posts: 5,095

Re: Create array of price change combinations

This becomes a relatively easy problem if you skip creating DIFF.  Just transpose PRICE instead of transposing DIFF.  Then you can compare sets of 2 elements within the array and track the maximum difference.

Super User
Super User
Posts: 7,421

Re: Create array of price change combinations

No, you misunderstand, maybe not use min/max, I meant current day - first day.

Super Contributor
Posts: 259

Re: Create array of price change combinations

Did you see the updated I posted with your code? It doesn't correctly calulcate the max price increase (2.3%). for 7 days, the code needs to do 21 calculations and take a mix. If there is an easier way i would definitely be open to it but the solution should give a maz of 2.3% (88/86-1) in any case. 

Super Contributor
Posts: 259

Re: Create array of price change combinations

I'm not sure I follow, would you mind elaborating
Super User
Posts: 5,095

Re: Create array of price change combinations

Here's some detail on transposing first, then computing differences afterwards:

 

proc transpose data=have prefix=price_ out=want;

by year month week;

var price;

run;

 

data want;

set want;

array price_ {*};

do i=1 to dim(price) - 1;

   do j=i+1 to dim(price);

      max_change = max(max_change, price_{j} / price_{i} - 1);

   end;

end;

run;

 

You don't need to compute and save all the % differences.  Just compute them one at a time, and track the maximum value.

 

I think I got the formulas right here, but those are easy to change if they're wrong.

Solution
‎04-05-2017 12:18 PM
Super Contributor
Posts: 259

Re: Create array of price change combinations

Hi Astounding

 

When I run the code below:

 

data have;
infile datalines;
input Year Month $ Week Day Price;
datalines;
2015 Jan 1 1 98
2015 Jan 1 2 87
2015 Jan 1 3 86
2015 Jan 1 4 86
2015 Jan 1 5 86
2015 Jan 1 6 87
2015 Jan 1 7 88
;
run;

proc transpose data=have prefix=price_ out=want;
by year month week;
var price;
run;

data want;
set want;
array price_ {*};
do i=1 to dim(price) - 1;
do j=i+1 to dim(price);
max_change = max(max_change, price_{j} / price_{i} - 1);
end;
end;
run;

 

I get the error

 

 

2123 data want;
2124 set want;
2125 array price_ {7};
2126 do i=1 to dim(price) - 1;
ERROR: The DIM, LBOUND, and HBOUND functions require an array name for
the first argument.
2127 do j=i+1 to dim(price);
ERROR: The DIM, LBOUND, and HBOUND functions require an array name for
the first argument.
2128 max_change = max(max_change, price_{j} / price_{i} - 1);
2129 end;
2130 end;
2131 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete. When this step was
stopped there were 0 observations and 15 variables.
WARNING: Data set WORK.WANT was not replaced because this step was
stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Super User
Posts: 5,095

Re: Create array of price change combinations

My fault.  Wrong:

 

dim(price)

 

Right:

 

dim(price_)

 

The DIM function requires the name of the array as an argument.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 21 replies
  • 226 views
  • 1 like
  • 6 in conversation