SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Sorting Bloomberg Data

Reply
Occasional Contributor
Posts: 5

Sorting Bloomberg Data

Hi SAS users,

I have uploaded data from Bloomberg and the data is sorted in a columns as follow

Company1Company2Company2022
Data1-1X1-1 (these are numbers for Tobin's Q)Date2-1X2-1 (these are numbers for Tobin's Q)Date2022-1X2022-1
Date1-2Y1-2Date2-2Y2-2Date2022-2Y2022-2
Date1-3Z1-3Date2-3Z2-3Date2022-3Y2022-3

I would like to sort the data this way

CompanyNameTobin'sQDate
Company1X1-1Date1-1
Company1Y1-2Date1-2
Company1Z1-3Date1-3
Company2X2-1Date2-1
------------
Compnay2022Y2022-3Date2022-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

Valued Guide
Posts: 3,208

Re: Sorting Bloomberg Data

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

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 1 reply
  • 233 views
  • 0 likes
  • 2 in conversation