Data not merging correctly

[a079011]

I am fairly new to SAS but have “some” experience. Thanks to that I have been giving the task of de-bugging someone else’s code that no longer supports SAS. I Think I know what is happening but don’t know how to fix it. I have skimmed everything down in order to ask this question.

I have two input files that I want to merge together; JOBS & ESP
JOBS
Obs     JOB                   APPL            QUAL                                                                      
1         PCRECW30      ACREC03                                    

ESP
Obs TYP      JOB                   APPL            E_COND                                                              
1      JOB     PCRECW30      AABCC01    CCCHK RC(5:4095) FAIL
2      JOB     PCRECW30      AABCC01    CCCHK RC(0:4) OK    

I have a mergerstatement in my SAS
DATA ESPJOBS AONLY BONLY;         
MERGE ESP(IN=A) JOBS(IN=B);     
BY JOB;                         
IF A AND B THEN OUTPUT ESPJOBS; 
IF A AND NOT B THEN OUTPUT AONLY;
IF B AND NOT A THEN OUTPUT BONLY;
PROC PRINT DATA=ESPJOBS;
TITLE 'ESPJOBS';       

ESPJOBS
Obs     TYP      JOB                   APPL            E_COND           QUAL
1         JOB      PCRECW30     ACREC03     CCCHK             RC(5:4095) FAIL       
2         JOB      PCRECW30     AABCC01     CCCHK             RC(0:4) OK            

For the second observation of the ESPJOBS output the appl column is incorrect. It should be ACREC03. I think because I have a different amount of observations that contain the same job name in my input file? Is this enough information to help me or should I add more data?

Thank you!

[Haikuo · Accepted solution]

The following suggestion was not correct.

Try reversing the merge order:

MERGE JOBS(IN=B) ESP(IN=A) ;  

A quick-dirty fix may be:

MERGE ESP(IN=A RENAME=APPL=ESP_APPL) JOBS(IN=B);

APPL=COALESCEC(ESP_APPL,APPL);

DROP ESP_APPL;

However, Merge is not recommended to use in "one to many" or "many to many" scenario in general unless you know exactly what you doing (even then, it will still be difficult when debugging), Proc SQL joins may provide what you need.

To learn more about "Merge", start with this excellent paper by :

http://www.ats.ucla.edu/stat/sas/library/nesug99/ad155.pdf

Haikuo

[a079011]

That worked. It helped me to identify another issue i need to look in to. Thank you!