BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mmmark
Fluorite | Level 6

Hell everyone!

I have a dataset on SAS with about a million option prices arranged with the following fields/columns:

DATE, COMPANY, PUT/CALL, PRICE

The PUT/CALL variable is an indicator variable that comes out as either  PUT or CALL for each unique DATE-COMPANY combination.

Example with numbers:

DATE             COMPANY          PUT/CALL       PRICE

2001/01/01       XOM                  PUT                10

2001/01/01       XOM                  CALL              12

2001/01/01       ABB                  PUT                11

2001/01/01       ABB                  CALL              13

What I need is for my table to be arranged with:

DATE, COMPANY, PUT PRICE, CALL PRICE


The above example with numbers, the output should be:


Example with numbers:

DATE             COMPANY          PUT PRICE      CALL PRICE

2001/01/01       XOM                  10                    12

2001/01/01       ABB                   11                   13

Would someone know how I could use SAS or any other software to complete this? Any help would definitely be appreciated!

1 ACCEPTED SOLUTION
2 REPLIES 2
RichardinOz
Quartz | Level 8

You can simply use Proc Transpose provided that your data is arranged by date and so that put and call records are consecutive, not necessarily always in the same order.  Otherwise data will have to be sorted which may be a problem if you are short on disk space.  Because the company names may not be in alphabetical order you may need to use the NOTSORTED option.  In the event that the put and call records for a company can come in either order I suggest using a view to prepare the data:

Data revised / view = revised ;

    Set have ;

  _Name_ = catt(put_call, '_Price') ;

Run ;

Proc Transpose …

Of course, if you are having to write data step code you may want to do it all there:

Data want ;

  Set have ;

  By Date

  Notsorted company

  ;

  Retain Put_Price

  Call_Price 0 ;

  ;

  If First.Company then

       Do ;

            Put_Price = . ;

            Call_Price = . ;

       End ;

  If put_call = 'PUT'

       Then Put_Price = Price ;

       Else  Call_Price = Price ;

  If Last.Company

       Then output ;

  Drop Put_Call

       Price

  ;

Run ;

Richard

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 372 views
  • 0 likes
  • 3 in conversation