11-09-2013 02:52 AM
Hi SAS users,
I have uploaded data from Bloomberg and the data is sorted in a columns as follow
|Data1-1||X1-1 (these are numbers for Tobin's Q)||Date2-1||X2-1 (these are numbers for Tobin's Q)||Date2022-1||X2022-1|
I would like to sort the data this way
I have tried Proc Transpose but it will not produce the desired results. Would arrays help? if so how. Any ideas are welcome.
11-09-2013 08:04 AM
The transpose is the first idea. Perhaps more easy to do some datasetcoding.
- Define two array-s for all the company 1/ date vars 2/ tobinsq. Pehaps the naming is helping.
SAS(R) 9.4 Statements: Reference (array)
- the in a do loop for all variables write to a SAS new dataset: company-id , date(i),tobinq(i)
- let te datastep proceed for all records.
The result will be a dataset with the columns you need, just the order is different.
Ordering the data can be done by creating indexes. Base SAS(R) 9.4 Procedures Guide
Or by sorting. Proc sort (company/date)
As the dataset is that small it could easily be fit in memory or being buffered. Unless there are many timemoments (10 minute snap-shots for over many years.)