topic Re: DI requirement on transpose in SAS Programming

DI requirement on transpose

vickys — Sat, 28 Apr 2018 02:48:16 GMT

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

Re: DI requirement on transpose

Ksharp — Sat, 28 Apr 2018 10:47:26 GMT

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;

Re: DI requirement on transpose

vickys — Sat, 28 Apr 2018 14:35:13 GMT

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

Re: DI requirement on transpose

art297 — Sat, 28 Apr 2018 17:14:34 GMT

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

Re: DI requirement on transpose

vickys — Sun, 29 Apr 2018 10:44:33 GMT

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

Re: DI requirement on transpose

art297 — Sun, 29 Apr 2018 13:34:49 GMT

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

Art, CEO, AnalystFinder.com

Re: DI requirement on transpose

vickys — Sun, 29 Apr 2018 14:06:45 GMT

Great My apologies. It works.

Many Thanks art.

Regards,
Swarup

Re: DI requirement on transpose

vickys — Sun, 29 Apr 2018 14:07:54 GMT

Many Thanks Kahsarp