topic Re: Merge SAS Dataset in SAS Data Management

Merge SAS Dataset

AshokD — Thu, 18 Aug 2016 10:37:36 GMT

I have two datasets.

DATASET :- A

EE_ID PRJ_CD

100055 406

100055 475

100055 492

300028 475

DATASET :- B

EE_ID SSN EMP_CD

100055 0100055 E

300028 0300028 E

I have sorted & merged the above two datasets by EE_ID.

DATA
FINAL
;
MERGE
A

B ;
BY
EE_ID
;

IF A THEN OUTPUT;

RUN;

Output:-

EE_ID PRJ_CD SSN EMP_CD

100055 475
100055 492
100055 406 0100055 E
300028 475 0300028 E

SSN and EMP_CD is getting populated for only one row and the remaining two rows are empty for the EE_ID 100055.

My expectation is :-

EE_ID PRJ_CD SSN EMP_CD

100055 475 0100055 E
100055 492 0100055 E
100055 406 0100055 E
300028 475 0300028

Any help would be appreciated.

Thanks!

Re: Merge SAS Dataset

Kurt_Bremser — Thu, 18 Aug 2016 10:47:33 GMT

a) don't write code in capitals; causes eyesore

b) use the "little running man" icon while composing a post to open a window for entering code; this makes example code more readable

c) use proper indentation to make code more readable

d) use the in= dataset option to create boolean variables that indicate when a dataset contributes to a merge or set:

data have_a;
input ee_id prj_cd;
cards;
100055 406
100055 475
100055 492
300028 475
;
run;

data have_b;
input ee_id ssn emp_cd $;
cards;
100055 0100055 E
300028 0300028 E
;
run;

data want;
merge
  have_a (in=a)
  have_b (in=b)
;
by ee_id;
if a;
run;

proc print noobs;
run;

The output from the above code looks like this:

 ee_id    prj_cd      ssn     emp_cd

100055      406     100055      E   
100055      475     100055      E   
100055      492     100055      E   
300028      475     300028      E

Re: Merge SAS Dataset

Astounding — Thu, 18 Aug 2016 12:15:46 GMT

The result you are describing looks like what you would get if you played with the program many times in many ways, in an attempt to get it to work. What actually happened somewhere along the way was that the variables SSN and EMP_CD got added to the data set A (somewhere along the way). Then when you merge, those values get read in and overwrite the values from data set B.

DROP the variables you don't need from the data set A. That should take care of the problem.