How can I better set up my SAS dataset?

[mmmark]

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!

[Reeza · Accepted solution]

Proc transpose.

SAS Learning Module: How to reshape data long to wide using proc transpose

[RichardinOz]

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