Stuck on a Merge Question

[InfoAlisaA]

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

[Linlin]

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

[shivas]

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