Transpose data

[cedre]

Hello,

The data for a large sample of stocks is in the format of message:

Ric          Date        Name       Value

Stock 1   6/1/2018
                            . BID           55.8
                            . BIDSIZE   1450
                            . BID_Time 13:50:00
Stock 1  6/1/2018
                             . ASK          55.84
                             . ASKSIZE   75
                             . ASK_TIME 13:50:01

I am trying to transpose this data by Ric, date and by one variable for "time" (instead of Bid_Time and Ask_Time separately):

Ric          Date         Time          Bid    Bidsize   Ask      Asksize 

Stock 1   6/1/2018   13:50:00    55.8  1450

Stock 1   6/1/2018    13:50:01                          55.84   75

...

Stock 2 

Thanks in advance

[Reeza]

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

Are you actually missing the stock names in the subsequent rows? That looks like a report not a data set.

---

@cedre wrote:

Hello,

The data for a large sample of stocks is in the format of message:

Ric          Date        Name       Value

Stock 1   6/1/2018
                            . BID           55.8
                            . BIDSIZE   1450
                            . BID_Time 13:50:00
Stock 1  6/1/2018
                             . ASK          55.84
                             . ASKSIZE   75
                             . ASK_TIME 13:50:01

I am trying to transpose this data by Ric, date and by one variable for "time" (instead of Bid_Time and Ask_Time separately):

Ric          Date         Time          Bid    Bidsize   Ask      Asksize 

Stock 1   6/1/2018   13:50:00    55.8  1450

Stock 1   6/1/2018    13:50:01                          55.84   75

...

Stock 2 

Thanks in advance

---

[cedre]

Hi Reeza.

yes, it a report. Actually, it is a message including updates. I am not missing any variable, for each RIc and date, the bid, bidsize and time are reported. 

[Reeza]

I was referring to if RIC and DATE were repeated on each line, if not, you may need to do that before you can use PROC TRANSPOSE. Look into RETAIN to set RIC and DATE across all rows and then you can use PROC TRANSPOSE to get the wide structure desired.

[cedre]

Thanks for this. For some reason, I can't get the retain statement right to set the _RIC and the date. Any suggesting please?  

[andreas_lds · Accepted solution]

Post the code you have, it is easier to to suggest a fix if we see what to fix.

[cedre]

please find it below. thanks in advance.

data want;
set have;
by RIC Date id notsorted;

if first.id then do;
RIC1 = RIC;
Date1 = Date;

end;
else do; /* 2nd to last obs in a group */
if RIC=. then RIC1=RIC;
if Date=. then Date1=Date;

retain RIC1 Date1;
format Date1 yymmdd10.;
end;
run;