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!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.