Create array of price change combinations

[brophymj]

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;

[Astounding]

OK, let's try this additional change:

array price_ {*} price_: ;

[rogerjdeangelis]

Create array of price change combinations

This is best done in IML or R.

In order to print the max on the first record I had to load a two dimesional array and
find the max of all elements and then load load and output a seven element
array

redirect to this message
https://goo.gl/6gxqsO
https://communities.sas.com/t5/Base-SAS-Programming/Create-array-of-price-change-combinations/m-p/347274

HAVE
====

Up to 40 obs WORK.HAVE total obs=7

Obs    DAY    PRICE

 1      1     100.0
 2      2     101.5
 3      3     103.0
 4      4     104.5
 5      5     106.0
 6      6     107.5
 7      7     109.0

WANT
====

DETAILS

 DAY1  PCT2 = 100*(Price[2]/Price[1] - 1) ... PCT7=100*(Price[7]/Price[1] - 1)
 ..
 DAY6  PCT7 = 100*(Price[7]/Price[6] - 1) 100*(109/107.5 - 1) = 1.3953488372


*
 ___  __ _ ___
/ __|/ _` / __|
\__ \ (_| \__ \
|___/\__,_|___/

;

 DAY    BASE     PCT1    PCT2    PCT3    PCT4    PCT5    PCT6    PCT7  MAX

 1.0    100.0     .      1.5     3.0     4.5     6.0     7.5     9.0   9.0
 2.0    101.5     .       .      1.5     3.0     4.4     5.9     7.4    .
 3.0    103.0     .       .       .      1.5     2.9     4.4     5.8    .
 4.0    104.5     .       .       .       .      1.4     2.9     4.3    .
 5.0    106.0     .       .       .       .       .      1.4     2.8    .
 6.0    107.5     .       .       .       .       .       .      1.4    .  PCT7 =100*(109/107.5 - 1) = 1.3953488372

*                         ____
__      ___ __  ___      |  _ \
\ \ /\ / / '_ \/ __|_____| |_) |
 \ V  V /| |_) \__ \_____|  _ <
  \_/\_/ | .__/|___/     |_| \_\
         |_|
;
 40 obs WORK.WANT total obs=6

Obs    DAY    PRICE    PCT2    PCT3    PCT4    PCT5    PCT6    PCT7    MAX

 1     1.0    100.0    1.5     3.0     4.5     6.0     7.5     9.0     9.0
 2     2.0    101.5     .      1.5     3.0     4.4     5.9     7.4      .
 3     3.0    103.0     .       .      1.5     2.9     4.4     5.8      .
 4     4.0    104.5     .       .       .      1.4     2.9     4.3      .
 5     5.0    106.0     .       .       .       .      1.4     2.8      .
 6     6.0    107.5     .       .       .       .       .      1.4      .



FULL SOLUTION
=============

*                _                  _       _
 _ __ ___   __ _| | _____        __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|      \__,_|\__,_|\__\__,_|

;

options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.have;
retain day price;
input Price;
day=_n_;
cards4;
100
101.5
103
104.5
106
107.5
109
;;;;
run;quit;

*_        ______  ____        ____
\ \      / /  _ \/ ___|      |  _ \
 \ \ /\ / /| |_) \___ \ _____| |_) |
  \ V  V / |  __/ ___) |_____|  _ <
   \_/\_/  |_|   |____/      |_| \_\

;

%utl_submit_wps64('
libname sd1 "d:/sd1";
options set=R_HOME "C:/Program Files/R/R-3.3.2";
libname wrk "%sysfunc(pathname(work))";
libname hlp "C:\Program Files\SASHome\SASFoundation\9.4\core\sashelp";
proc r;
submit;
source("C:/Program Files/R/R-3.3.2/etc/Rprofile.site", echo=T);
library(haven);
have<-t(read_sas("d:/sd1/have.sas7bdat")[,2]);
mat=have[rep(seq_len(nrow(have)), each=6),];
bas<-cbind(have[1:6],mat);
fin<-100*(bas[,2:8]/bas[,1] - 1);
fin[lower.tri(fin)] <- NA;
fin[ row(fin) == col(fin) ] <- NA;
fin[1,1]<-max(fin,na.rm = TRUE);
endsubmit;
import r=fin data=wrk.fin;
run;quit;
');

data want;
  retain day price pct2-pct7 max;
  merge sd1.have(obs=6) fin(rename=(v2-v7=pct2-pct7 v1=max));
run;quit;

proc print data=want width=min;
 format _numeric_ 8.1;
run;quit;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

%symdel obs /nowarn;
libname sd1 "d:/sd1";
data want;
  retain day base maxfin;
  if _n_=0 then do;
     rc=%sysfunc(dosubl('proc sql noprint;select count(*) into :obs separated by ""  from sd1.have;quit;'));
  end;
  do rec=1 to &obs;
     array pry[&obs] jan1-jan&obs;
     array pct[&obs] pct1-pct&obs;
     array fin[&obs,&obs] fin1-fin%eval(%eval(&obs * &obs));
     set sd1.have;
     pry[rec]=price;
  end;
  do day=1 to &obs - 1;
    do mat=day+1 to &obs;
        fin[day,mat]=100*pry[mat]/pry[day] -100;
        base=pry[day];
    end;
  end;
  maxfin=max(of fin[*]);
  put maxfin=;
  do day=1 to &obs - 1;
    do mat=1 to &obs;
       fins+1;
       pct[mat]=fin[day,mat];
    end;
    if day ne 1 then maxfin=.;
    output;
  end;
  keep day base  pct: maxfin;
run;quit;

proc print data=want width=min;
 format _numeric_ 8.1;
run;quit;

[Ksharp]

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;

[brophymj]

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

[ballardw]

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

[brophymj]

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