SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cedre
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19
Post the code you have, it is easier to to suggest a fix if we see what to fix.

View solution in original post

6 REPLIES 6
Reeza
Super User

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
Calcite | Level 5

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
Super User
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
Calcite | Level 5

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
Jade | Level 19
Post the code you have, it is easier to to suggest a fix if we see what to fix.
cedre
Calcite | Level 5

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1244 views
  • 0 likes
  • 3 in conversation