Hi SAS users,
I have uploaded data from Bloomberg and the data is sorted in a columns as follow
Company1 | Company2 | Company2022 | |||
---|---|---|---|---|---|
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 |
Date1-2 | Y1-2 | Date2-2 | Y2-2 | Date2022-2 | Y2022-2 |
Date1-3 | Z1-3 | Date2-3 | Z2-3 | Date2022-3 | Y2022-3 |
I would like to sort the data this way
CompanyName | Tobin'sQ | Date |
---|---|---|
Company1 | X1-1 | Date1-1 |
Company1 | Y1-2 | Date1-2 |
Company1 | Z1-3 | Date1-3 |
Company2 | X2-1 | Date2-1 |
---- | ---- | ---- |
Compnay2022 | Y2022-3 | Date2022-3 |
I have tried Proc Transpose but it will not produce the desired results. Would arrays help? if so how. Any ideas are welcome.
Thank you
The transpose is the first idea. Perhaps more easy to do some datasetcoding.
The thoughts:
- 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.)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.