SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Merge SAS Dataset

Reply
New Contributor
Posts: 3

Merge SAS Dataset

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!

Super User
Posts: 6,942

Re: Merge SAS Dataset

[ Edited ]

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   

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,085

Re: Merge SAS Dataset

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.

Ask a Question
Discussion stats
  • 2 replies
  • 226 views
  • 0 likes
  • 3 in conversation