topic Re: Stuck on a Merge Question in SAS Procedures

Stuck on a Merge Question

InfoAlisaA — Sun, 13 May 2012 22:32:23 GMT

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

Re: Stuck on a Merge Question

Linlin — Mon, 14 May 2012 00:13:21 GMT

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

Re: Stuck on a Merge Question

shivas — Mon, 14 May 2012 05:01:37 GMT

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