topic Re: Transposing of data in SAS Data Management

Transposing of data

Kyojik — Mon, 19 Mar 2018 18:50:55 GMT

Hello Friends,
I am trying to transpose some data which looks like following
Company Variable 2000 2001 and so on till 2017.

1 A - -

1 b - -

2 A - -

2 B - -

3 A - -

3 B - -;

I tried following code :
DATA created;
SET have;
ARRAY ay(2000:2017) y2000 - y2017 ;
DO year = 2000 to 2017 ;
y = ay(year);
OUTPUT;
END;
DROP y2000 - y2017 ;
RUN;

This gives me following dataset;

Company Variable year data

1 A - -

1 b - -

2 A - -

2 B - -

3 A - -

3 B - -

But I want variables as column heads

Company Year B A

1 2000 - -

1 2001 - -

2 2000 - -

2 2001 - -

3 2000 - -

3 20001 - -

Please suggest something which could help this from first step or all they way fresh.

I have also added excel datafile (sheet1)
Thanks

Re: Transposing of data

TomKari — Mon, 19 Mar 2018 21:09:54 GMT

I think this is pretty close.

proc sort data=have out=inter1;
	by Company;
run;

proc transpose data=inter1 out=want;
	by Company;
	id Variable;
	var y2000 y2001 y2002;
run;

Tom

Re: Transposing of data

art297 — Mon, 19 Mar 2018 22:13:58 GMT

You have to provide a better description of what you have and what you want!

I tried it to produce a file that had: isin year 1591_TT_Equity 1665_HK_Equity 1693_HK_Equity

1695_HK_Equity 2112_HK_Equity 6113_HK_Equity 6163_HK_Equity 685_HK_Equity

705_HK_Equity 8423_TT_Equity 8427_HK_Equity AAB_MK_Equity AARETB_MK_Equity

AAX_MK_Equity ABANK_MK_Equity ABB_MK_Equity

but there were multiples of those variables for some ISIN/year combinations.

Thus, given your actual data, what do you want the transposed file to include and look like? And, while you're at it, explain what you want to do with ticker and year variables that have missing or "#N/A N/A values.

Art, CEO, AnalystFinder.com

Re: Transposing of data

Kyojik — Tue, 20 Mar 2018 08:34:42 GMT

Thank you Tom it was perfect.

Re: Transposing of data

Kyojik — Tue, 20 Mar 2018 08:36:37 GMT

THank you @art297. I just noted there were few missing ISIN which were creating this problem. The codes written by Tom worked. Thank you for you time.
Good day
Kyojik