DATA Step, Macro, Functions and more

Merge and Transpose at the Same Time

Accepted Solution Solved
Reply
Valued Guide
Posts: 620
Accepted Solution

Merge and Transpose at the Same Time

[ Edited ]

I have a transpose / merge question.  I have two tables. 

 

Table Dimension which contains columns name colname rank year yeartype employee category tenure and year conformed. 

 

Table data contains columns unitId Insitution_name and many measures with names that correlate to dimension.name.

 

My goal is to merge the two tables such that the numeric values are displayed side by side with the dimensional fields.  Effectively the employees column in the display is created from the measure column.  I will then merge each of the measure columns vertically.

 

Please advise on the merge process.

 

Desired output is below:

 

    Measure column          
UnitID Institution_Name Employees Rank year yearType employee_category tenure yearConformed
231624 College of William and Mary 174 Associate Professors 2016 S Faculty Tenured 1617
126818 Colorado State University-Fort Collins 329 Associate Professors 2016 S Faculty Tenured 1617
134097 Florida State University 294 Associate Professors 2016 S Faculty Tenured 1617
232186 George Mason University 337 Associate Professors 2016 S Faculty Tenured 1617
139940 Georgia State University 268 Associate Professors 2016 S Faculty Tenured 1617
153603 Iowa State University 378 Associate Professors 2016 S Faculty Tenured 1617
159391 Louisiana State University and Agricultural & Mechanical College 268 Associate Professors 2016 S Faculty Tenured 1617

Accepted Solutions
Solution
‎02-20-2018 02:25 PM
Valued Guide
Posts: 620

Re: Merge and Transpose at the Same Time

[ Edited ]
Posted in reply to DavidPhillips2

It looks like first I transpose the data then I merge it.


proc sort data=combined2;
by UnitID 'Institution Name'n;
run;

proc transpose data=combined2 out=testing;
var 'All ranks (S2012_SIS_RV Wi_0001'n 'No academic rank (S2012_SIS_0002'n 'No academic rank (S2012_SIS_0003'n;
by UnitID 'Institution Name'n;
run;

 

This paper is really good.

https://www.lexjansen.com/nesug/nesug06/hw/hw09.pdf

View solution in original post


All Replies
Solution
‎02-20-2018 02:25 PM
Valued Guide
Posts: 620

Re: Merge and Transpose at the Same Time

[ Edited ]
Posted in reply to DavidPhillips2

It looks like first I transpose the data then I merge it.


proc sort data=combined2;
by UnitID 'Institution Name'n;
run;

proc transpose data=combined2 out=testing;
var 'All ranks (S2012_SIS_RV Wi_0001'n 'No academic rank (S2012_SIS_0002'n 'No academic rank (S2012_SIS_0003'n;
by UnitID 'Institution Name'n;
run;

 

This paper is really good.

https://www.lexjansen.com/nesug/nesug06/hw/hw09.pdf

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 112 views
  • 0 likes
  • 1 in conversation