topic Re: Transposed data in SAS Procedures

Transposed data

kbl — Tue, 16 May 2017 16:47:14 GMT

Good afternoon,
I pulled some TAQ data, and the file is in a .CSV format. I've only kept (and am interested in ) about six variables. The data pulled just fine, but it is listed in a column, with all trades for one company listed, followed by all trades for the next company, etc. I need the file sorted so that i can see all companies that traded at a particular time (along with the number of shares traded). In essence, instead of having vertical data, I want horizontal data. Can you help? I am attaching one of the files so you can see what i mean. Thanks very much for any assistance you can provide.

Re: Transposed data

art297 — Tue, 16 May 2017 17:11:40 GMT

Can you attach an example showing how you want the resulting file to look?

Art, CEO, AnalystFinder.com

Re: Transposed data

Astounding — Tue, 16 May 2017 17:36:07 GMT

What you're asking for is a report. The data should remain in vertical form. A report might display the data in horizontal form, if that is useful to you. It becomes a question of whether it is worth it to you to learn some programming skills, to be able to produce the report that you want.

Re: Transposed data

kbl — Tue, 16 May 2017 20:17:18 GMT

Time	A	AA	AAPL	other stocks…
	price	price
	price

Art,

Thanks for your reply. The main shift I want is that I want each company' sym_Root to be a separate column. Time remains the first column. Then column two will be the first company that trades at a given time with the price it traded at for that time. The next column will be the second company and its trading price (if it traded at that time), etc.

Re: Transposed data

kbl — Tue, 16 May 2017 20:20:17 GMT

Good afternoon,

I have programmed before, although it's been a couple of years since i used SAS.

I need the tickers (SYM_ROOT) to be in a separate column for each stock with the price at which it is trading for a given time listed under the company ticker. As a result, I don't think it is sufficient to create a report; I think I need to transpose the data. Does that sound right?

Re: Transposed data

art297 — Tue, 16 May 2017 20:27:59 GMT

What you want to do is easy with proc transpose, but you have some duplicate entries for some companies at the same times. As long as the duplicates are irrelevant, you can get what you want with:

proc sort data=have nodupkey;
  by time_m sym_root;
run;

proc transpose data=have out=want (drop=_:);
  by time_m;
  id sym_root;
  var price;
run;

Art, CEO, AnalystFinder.com

Re: Transposed data

kbl — Tue, 16 May 2017 21:35:20 GMT

Thanks so much Art! This is exceptionally helpful. Can I trouble you for one more? I was going to do some other calculations in Excel, but I think it will be easier to use SAS for everything.

What changes do I need to make to the program if I want everything that you just sent, plus I want to keep the volume data by company. So, after I have a column for each company's price data, I have a set of columns for each company's volume data? I think there is not a duplicate issue; each time listed represents the last trade that minute for each company's stock, and we only need to sort to the minute (the first four digits), not to the second (the final digit in the time variable) . The data can look something like this:

Time (end of each minute)

Stock2

Stock 3

Stock 4

Stock 5

…

Stock 500

Stock2

Stock 3

Stock 4

Stock 5

…

30:46

36.5

100