Proc Transpose Data transpose question

[lulumohca]

Hi,

I am having a lot of trouble using SAS to transpose the data from an excel file.
I have attached the file with the input worksheet and what I wanted to get in the output.

If someone can help me with this using SAS or Excel, that would be great. It is taking me a long time to do it manually in Excel (and I have 80+ files of the same format to transpose, with different dates and PL variables)

Here is what the data looks like (truncated)

Input:

Output (highlighted cells is where I hid rows):

Thank you.

 

[lulumohca]

I couldn't get picture link to work, here is what the data looks like:


PL5 PL6
Date seats gross percent seats gross percent
08-Dec-2011 0 $0.00 5 0 $0.00 6
09-Dec-2011 0 $0.00 5 0 $0.00 6
10-Dec-2011 0 $0.00 5 0 $0.00 6
11-Dec-2011 0 $0.00 5 0 $0.00 6
12-Dec-2011 0 $0.00 5 0 $0.00 6
13-Dec-2011 0 $0.00 5 0 $0.00 6

and so on with more PLx's, and the output I wanted to get would look like this:

Date Last_Date Days_between PL seat_sold Gross price_per_seat
08-Dec-2011 15-Feb-2012 69 PL5 0 0
09-Dec-2011 15-Feb-2012 68 PL5 0 0
10-Feb-2012 15-Feb-2012 5 PL5 0 0
11-Feb-2012 15-Feb-2012 4 PL5 0 0
12-Feb-2012 15-Feb-2012 3 PL5 0 0
13-Feb-2012 15-Feb-2012 2 PL5 3 855 285
14-Feb-2012 15-Feb-2012 1 PL5 0 0
15-Feb-2012 15-Feb-2012 0 PL5 1 285 285
08-Dec-2011 15-Feb-2012 69 PL6 0 0
09-Dec-2011 15-Feb-2012 68 PL6 0 0
10-Dec-2011 15-Feb-2012 67 PL6 0 0
11-Dec-2011 15-Feb-2012 66 PL6 0 0
12-Dec-2011 15-Feb-2012 65 PL6 0 0
13-Dec-2011 15-Feb-2012 64 PL6 0 0
30-Jan-2012 15-Feb-2012 16 PL6 0 0
31-Jan-2012 15-Feb-2012 15 PL6 0 0
01-Feb-2012 15-Feb-2012 14 PL6 0 0
02-Feb-2012 15-Feb-2012 13 PL6 0 0
03-Feb-2012 15-Feb-2012 12 PL6 0 0
04-Feb-2012 15-Feb-2012 11 PL6 0 0

Thanks again