- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for this. For some reason, I can't get the retain statement right to set the _RIC and the date. Any suggesting please?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;