Create array of price change combinations

[brophymj]

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

Year	Month	Week	Day	Price			2	3	4	5	6	7
2015	Jan	1	1	100		1	1.5%	3.0%	4.5%	6.0%	7.5%	9.0%	9.0%
2015	Jan	1	2	101.5		2		1.5%	3.0%	4.4%	5.9%	7.4%
2015	Jan	1	3	103		3			1.5%	2.9%	4.4%	5.8%
2015	Jan	1	4	104.5		4				1.4%	2.9%	4.3%
2015	Jan	1	5	106		5					1.4%	2.8%
2015	Jan	1	6	107.5		6						1.4%

[RW9]

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.

[brophymj]

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

[RW9]

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;

[brophymj]

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

Mon	100
Tues	150	Max
Wed	110
Thur	90	Min
Friday	100

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

[brophymj]

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. 

[RW9]

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;

[brophymj]

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. 

[Astounding]

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.

[RW9]

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

[brophymj]

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. 

[brophymj]

I'm not sure I follow, would you mind elaborating

[Astounding]

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.

[brophymj · Accepted solution]

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

[Astounding]

My fault.  Wrong:

dim(price)

Right:

dim(price_)

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