topic How can I better set up my SAS dataset? in SAS Programming

How can I better set up my SAS dataset?

mmmark — Sun, 19 May 2013 05:20:01 GMT

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!

Re: How can I better set up my SAS dataset?

Reeza — Sun, 19 May 2013 05:28:58 GMT

Proc transpose.

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

Re: How can I better set up my SAS dataset?

RichardinOz — Sun, 19 May 2013 07:59:42 GMT

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