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

Transposing of data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Transposing of data

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

 


Accepted Solutions
Solution
‎03-20-2018 04:34 AM
PROC Star
Posts: 1,288

Re: Transposing of data

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

View solution in original post


All Replies
Solution
‎03-20-2018 04:34 AM
PROC Star
Posts: 1,288

Re: Transposing of data

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

Occasional Contributor
Posts: 15

Re: Transposing of data

Thank you Tom it was perfect.
PROC Star
Posts: 8,145

Re: Transposing of data

[ Edited ]

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

 

Occasional Contributor
Posts: 15

Re: Transposing of data

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 188 views
  • 2 likes
  • 3 in conversation