BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kyojik
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

4 REPLIES 4
TomKari
Onyx | Level 15

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

Kyojik
Obsidian | Level 7
Thank you Tom it was perfect.
art297
Opal | Level 21

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

 

Kyojik
Obsidian | Level 7
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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