Hello Everyone,
I am really stuck on this homework problem about merges.
So I have three data sets, these are all the partial listings of these data sets:
orion.organization_dim:
Partial orion.Organization_Dim
Manager_ Manager_ Manager_ Manager_ Manager_ Manager_
ObsEmployee_ID Level1 Level2 Level3 Level4 Level5 Level6
1 120101 120261 120259 . . . .
2 120102 120101 120261 120259 . . .
3 120103 120101 120261 120259 . . .
4 120104 120101 120261 120259 . . .
5 120105 120101 120261 120259 . .
orion.employee_addresses:
Partial orion.employee_addresses
Employee_
Obs ID Employee_Name
1 121044 Abbott, Ray
2 120145 Aisbitt, Sandy
3 120761 Akinfolarin, Tameaka
4 120656 Amos, Salley
5 121107 Anger, Rose
So what I am supposed to do is to merge these together to get manager names for each level of manager to output it to a new data set manager_names.
Example:
Partial manager_names Data
Manager_ Manager_ Manager_ Manager_ Manager_ Manager_
Obs Employee_ID Level1 Level2 Level3 Level4 Level5 Level6 Manager1_Name
420 121144 121142 121141 120261 120259 . . Steiber, Reginald
421 121145 121142 121141 120261 120259 . . Steiber, Reginald
422 121146 121141 120261 120259 . . . Bleu, Henri Le
423 121147 121142 121141 120261 120259 . . Steiber, Reginald
424 121148 121141 120261 120259 . . . Bleu, Henri Le
Manager5_ Manager6_
Obs Manager2_Name Manager3_Name Manager4_Name Name Name
420 Bleu, Henri Le Highpoint, Harry Miller, Anthony
421 Bleu, Henri Le Highpoint, Harry Miller, Anthony
422 Highpoint, Harry Miller, Anthony
423 Bleu, Henri Le Highpoint, Harry Miller, Anthony
424 Highpoint, Harry Miller, Anthony
Right now, I am completely clueless as to how to begin this.
This is my code so far:
proc sort data=orion.organization_dim out=organization_dim;
by Employee_ID;
run;
data tempt(keep=Manager_Level1 Manager_Level2 Manager_Level3 Manager_Level4 Manager_Level5 Manager_Level6);
set organization_dim;
set orion.employee_addresses;
run;
proc sort data=tempt out=mngr1;
by Manager_Level1;
run;
data manager1;
set mngr1;
set orion.employee_addresses (rename=(Employee_Name=Manager1_Name));
run;
I am just playing around right now, but if anyone could give me any pointers as to how I could gather this information it would be greatly appreciated.
Thanks,
Alisa
Hi Alisa,
I made up some data, hope it helps.
data employee_addresses;
input id name $;
cards;
1 Alisa
2 Linlin
3 FE
4 PG
5 Mike
6 Haikuo
7 Ksharp
8 Tom
9 Art
;
data Organization_Dim;
input id level1-level6;
cards;
1 3 4 6 7 8 9
2 3 4 6 7 8 9
3 4 5 6 8 9 .
4 5 6 8 9 . .
5 6 7 8 9 . .
6 8 9 . . . .
7 8 9 . . . .
8 9 . . . . .
9 . . . . . .
;
data temp(keep=id m i rename=(id=_id m=id));
set Organization_Dim;
by id;
array _l(*) level:;
do i=1 to 6 while(not missing(_l(i)));
m=_l(i);
output;end;
proc sort data=temp;
by id _id;
data merged;
merge temp employee_addresses(in=b);
by id ;
if b;
proc sort;
by _id i;
proc transpose data=merged prefix=level out=want( drop=_name_);
by _id;
var name;
run;
data want;
set want(where =(_id ne .));
rename _id=id;
run;
proc print;run;
Obs id level1 level2 level3 level4 level5 level6
1 1 FE PG Haikuo Ksharp Tom Art
2 2 FE PG Haikuo Ksharp Tom Art
3 3 PG Mike Haikuo Tom Art
4 4 Mike Haikuo Tom Art
5 5 Haikuo Ksharp Tom Art
6 6 Tom Art
7 7 Tom Art
8 8 Art
Hi,
Using Linlin data...Try this....
data employee_addresses;
input id name $;
cards;
1 Alisa
2 Linlin
3 FE
4 PG
5 Mike
6 Haikuo
7 Ksharp
8 Tom
9 Art
;
data fmt11 (keep=FMTNAME START END LABEL TYPE);
length FMTNAME $30. START END $256.;
set WORK.employee_addresses;
FMTNAME = 'test' ;
START = id;
END = START;
LABEL = name;
TYPE = 'N' ;
run;
proc format cntlin=fmt11 lib=work; run;
data Organization_Dim;
input id level1-level6 ;
cards;
1 3 4 6 7 8 9
2 3 4 6 7 8 9
3 4 5 6 8 9 .
4 5 6 8 9 . .
5 6 7 8 9 . .
6 8 9 . . . .
7 8 9 . . . .
8 9 . . . . .
9 . . . . . .
;
run;
data want;
set Organization_Dim;
array xx(7) id level1-level6;
array yy(7) $ newid newlevel1-newlevel6;
do i =1 to dim(xx);
yy(i)=put(xx(i),test.);
end;
drop i;
run;
Thanks,
Shiva
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.