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
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
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;
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
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
Check again! The dataset WANT should include the field NAME with the values you indicated.
Art, CEO, AnalystFinder.com
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.