topic Sorting Bloomberg Data in SAS Data Management

Sorting Bloomberg Data

Lilly2012 — Sat, 09 Nov 2013 07:52:10 GMT

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

Re: Sorting Bloomberg Data

jakarman — Sat, 09 Nov 2013 13:04:18 GMT

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.)