DATA Step, Macro, Functions and more

DI requirement on transpose

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

DI requirement on transpose

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


Accepted Solutions
Solution
3 weeks ago
PROC Star
Posts: 8,113

Re: DI requirement on transpose

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


All Replies
Super User
Posts: 10,615

Re: DI requirement on transpose

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;
Occasional Contributor
Posts: 9

Re: DI requirement on transpose

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

Occasional Contributor
Posts: 9

Re: DI requirement on transpose

Many Thanks Kahsarp
Solution
3 weeks ago
PROC Star
Posts: 8,113

Re: DI requirement on transpose

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

 

Occasional Contributor
Posts: 9

Re: DI requirement on transpose

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
PROC Star
Posts: 8,113

Re: DI requirement on transpose

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

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 9

Re: DI requirement on transpose

Great My apologies. It works.

Many Thanks art.

Regards,
Swarup
☑ This topic is solved.

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

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