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

Hi,

I had a requirement to do in DI studio(4.9), where I have to make the columns to rows and keeping the columns as it is. I tried with Tranpose Transformation creating two columns fro name and then with extract TXR, I am getting the format but the values are missing. Seems I am missing some where, but dont know where is the gap.

I really appreciate if some one help me. Pls...dont worry about the x values, may find some typo errors

for ex:

What I have is   

 
code    year    X1          X2            X3
101     2010  1254     7846         7823
101     2011  4569     1478         9812
101     2012  1245     9512         6547
101     2013  2369     4826         4563
102    2010   9876     1456         4578
102    2 011 1245      4589         1485
102    2012  1326      5464         6522
102    2013  4848      6666         1518
103    2010  5416      3269         1458
103    2011  6597       3247        4699
103    2012  6964      1456         5154
103    2013  1450      5464         6649

 

  I am trying to get the following output          

code year    name     X1       X2        X3
101  2010     X1      1254       .           .
101  2010     X2        .         7846       .
101  2010     X3         .          .         7823
101  2011     X1       4569     .            .
101  2011     X2         .        1478        .
101  2011     X3         .           .         9812
101  2012     X1       1245      .              .
101  2012     X2          .        9512       .  
101 2012      X3          .          .          6547
101 2013      X1       2369       .            .
101 2013      X2         .         4826        .
101 2013      X3         .           .           4563
102 2010      X1       9876       .             .
102 2010      X2          .        1456         .
102 2010      X3          .          .             4578
102 2011      X1      1245         .              .
102 2011     X2          .           4589          .
102 2011     X3          .             .           1485
102 2012     X1       1326         .               .
102 2012     X2         .           5464           .
102 2012     X3          .             .            6522
102 2013     X1      4848          .               .
102 2013     X2         .            6626        .
102 2013     X3         .             .            1518
                                     

 

Thank you.

 

Vicks

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

I think the following will do what you want:

proc transpose data=have out=temp name=name;
  by code year;
  var x1-x3;
run;

proc transpose data=temp out=want(drop=_:);
  by code year name;
  id name;
  var col1;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post

7 REPLIES 7
Ksharp
Super User
data have;
input code    year    X1          X2            X3;
cards;
101     2010  1254     7846         7823
101     2011  4569     1478         9812
101     2012  1245     9512         6547
101     2013  2369     4826         4563
102    2010   9876     1456         4578
102    2011 1245      4589         1485
102    2012  1326      5464         6522
102    2013  4848      6666         1518
103    2010  5416      3269         1458
103    2011  6597       3247        4699
103    2012  6964      1456         5154
103    2013  1450      5464         6649
;
run;
proc transpose data=have out=temp;
by code year;
var x1-x3;
run;
proc transpose data=temp out=want(drop=_:);
by code year _name_;
id _name_;
var col1;
run;
vickys
Obsidian | Level 7

Thanks, Ksharp for quick response.

I ran the query and looks good.I really appreciate that.

But  x1, x2, x3 are not  coming as observations, while they are at variables...wondering any tuning of code can be done.

 

Thanks,

Vickys

vickys
Obsidian | Level 7
Many Thanks Kahsarp
art297
Opal | Level 21

I think the following will do what you want:

proc transpose data=have out=temp name=name;
  by code year;
  var x1-x3;
run;

proc transpose data=temp out=want(drop=_:);
  by code year name;
  id name;
  var col1;
run;

Art, CEO, AnalystFinder.com

 

vickys
Obsidian | Level 7
Thanks, art.

I think its the same output, any thing to add to get the name column as well like..

name X1 X2 X3
x1 678 . .
x2 . 456 .
x3 . . 753

Thanks,
Vickys
art297
Opal | Level 21

Check again! The dataset WANT should include the field NAME with the values you indicated.

 

Art, CEO, AnalystFinder.com

 

vickys
Obsidian | Level 7
Great My apologies. It works.

Many Thanks art.

Regards,
Swarup

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 968 views
  • 2 likes
  • 3 in conversation