topic Re: Transpose data in SAS Programming

Transpose data

cedre — Fri, 15 Feb 2019 21:14:21 GMT

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

Re: Transpose data

Reeza — Fri, 15 Feb 2019 21:20:15 GMT

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

Re: Transpose data

cedre — Fri, 15 Feb 2019 21:24:33 GMT

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.

Re: Transpose data

Reeza — Fri, 15 Feb 2019 21:28:11 GMT

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.

Re: Transpose data

cedre — Sat, 16 Feb 2019 00:03:09 GMT

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

Re: Transpose data

andreas_lds — Sat, 16 Feb 2019 06:35:52 GMT

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

Re: Transpose data

cedre — Sat, 16 Feb 2019 11:33:51 GMT

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;