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;
... View more