Transposing price details but retaining dates corresponding to prices

[brophymj]

Below is a test sample of price data. The actual dataset is much larger (400 rows or more) and loads of different types of coins (BBT is the example used here). Each price has a date associated with it. The code below transposes the data and calculates the maximum price increase over the period from any day to any corresonding day. You will see when you run this that the max return is 2.33%. This is the price increase from 86 (which occurred first on 20150201) and to 88 (which occurred on 20150205).

The code below correctly calculates the return but I need to know which prices and dates produced that return so i can go through the price history and find it on a graph. So, for getting the prices, it just needs to be the particular price{j} and price{i} in the loop that produces the max return. I think I can get this by inserting the following into the code:

if (price{j} / price{i}-1) ge max_change then do;
maxprice = price{j};
minprice = price{i};
end;

However, is there a way I can identify the dates corresponding to those prices? When I transpose the date the the dates are dropped. I would like to retain this information somehow while still making the calculation work. When transposing, Is it possible to label the price_1, price_2, ... with the dates and then look up the date corresponding to a price (like a hlookup in excel)??

Thanks

data have;
infile datalines;
input Coin $ Date Price;
datalines;
BBT 20150130 98
BBT 20150131 87
BBT 20150201 86
BBT 20150202 86
BBT 20150203 86
BBT 20150204 87
BBT 20150205 88
;
run;

proc transpose data=have prefix=Price_ out=have;
by Coin ;
var Price;
run;

data have1;
set have;
array price{*} 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;
min = min(of price[*]);
max = max(of price[*]);
run;

[Astounding]

You can get the dates, but you have to change OUT= on PROC TRANSPOSE.  Otherwise it is wiping out the only data set that contains the dates.

So assuming you have the HAVE data set coming out of PROC SORT, here is code you can add to your final DATA step.  First, inside the DO loop you have to change the MAX_CHANGE calculations to track the dates that you want.  For example:

change = price{j} / price{i} - 1;

if change > max_change then do;

   max_change = change;

   date1 = i;

   date2 = j;

end;

Then at the end of the DATA step, add code to retrieve those dates from the HAVE data set.  The full DATA step might become:

data have1;
set transposed_have;
array price{*} Price_:;
do i=1 to dim(price) - 1;
do j=i+1 to dim(price);

change = price{j} / price{i} - 1;

if change > max_change then do;

   max_change = change;

   date1 = i;

   date2 = j;

end;

end;
end;
min = min(of price[*]);
max = max(of price[*]);

coin_count=0;

do until (last.coin);

   set original_have (keep=coin date);

   by coin;

   coin_count + 1;

   if coin_count=i then date1=date;

   else if coin_count=2 then date2=date;

end;
run;

As usual, the code is untested so there might be debugging needed.  And the data set names have to match what you produce as your earlier data sets.

[brophymj]

Hi Asotunding 

Thanks for the reply. The only thing I'm not sure of is what you mean by change "out = on PROC Transpose"

So, in the code

proc transpose data=have prefix=Price_ out=have;
by Coin ;
var Price;
run;

where do I change it so that the data is included? When the price is transposed, it becomes 7 fields. In the actual dataset there will be 100s of coins but the dates will be common to each coin. Would you mind clarifying the adjustment to the proc transpose code you suggested? 

Thanks

[Astounding]

You could make this change:

proc transpose data=have out=have_transposed prefix=Price_;

That gives you a second data set to work with, while preserving the original data set.  The transposed data set contains exactly the same variables as it did before, but the original HAVE data set still contains all the dates.  (I think I might have referred to it as HAVE_ORIGINAL instead of HAVE in my sample solution.)