topic Re: Create array of price change combinations in SAS Programming

Create array of price change combinations

brophymj — Wed, 05 Apr 2017 09:49:03 GMT

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%

Re: Create array of price change combinations

RW9 — Wed, 05 Apr 2017 10:11:17 GMT

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.

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 10:22:06 GMT

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

Re: Create array of price change combinations

RW9 — Wed, 05 Apr 2017 10:36:35 GMT

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;

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 10:47:19 GMT

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.

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 10:55:01 GMT

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.

Re: Create array of price change combinations

RW9 — Wed, 05 Apr 2017 11:03:52 GMT

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

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 11:11:57 GMT

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.

Re: Create array of price change combinations

RW9 — Wed, 05 Apr 2017 11:14:44 GMT

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;

Re: Create array of price change combinations

Astounding — Wed, 05 Apr 2017 11:22:38 GMT

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.

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 11:41:28 GMT

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

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 12:29:32 GMT

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.

Re: Create array of price change combinations

Astounding — Wed, 05 Apr 2017 13:49:10 GMT

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;

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.

Re: Create array of price change combinations

Ksharp — Wed, 05 Apr 2017 14:01:38 GMT

you really should take on IML code.
It is very easy for IML.




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;

proc iml;
use have;
read all var{price};

lag=lag(price,1:nrow(price)-1);
dif=dif(price,1:nrow(price)-1);
d=dif/lag; 

w=j(nrow(d)-1,ncol(d),.);
do i=1 to ncol(d);
 w[1:nrow(d)-i,i]=d[i+1:nrow(d),i];
end;
want=j(nrow(d)-1,ncol(d),.);
do i=1 to nrow(w);
 want[i,i:ncol(d)]=w[i,1:(ncol(d)-i+1)];
end;

create temp from want;
append from want;
close;
quit;

data want;
 merge have temp;
 max=max(of col:);
run;
proc print;run;

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 14:47:58 GMT

I would but I don't have the SAS/IML package. This code doesn't work on my 9.3 SAS version.

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 15:26:55 GMT

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

Re: Create array of price change combinations

ballardw — Wed, 05 Apr 2017 15:35:07 GMT

@Ksharp The op seems unable to describe the process or the result in non-speadsheet terms. This is a continuation of https://communities.sas.com/t5/Base-SAS-Programming/Max-price-change-within-interval-i-e-30-days-or-365-days/m-p/347126#M80136

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 15:43:05 GMT

I thought I described it quite clearly. I don't think your comment helps.

Re: Create array of price change combinations

Astounding — Wed, 05 Apr 2017 15:50:19 GMT

My fault. Wrong:

dim(price)

Right:

dim(price_)

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

Re: Create array of price change combinations

brophymj — Wed, 05 Apr 2017 16:10:12 GMT

Thanks Astounding.

I made the change (just replaced price with price_) but I'm still getting errors in the log. If you run the code below do you get errors?

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;