topic Data not merging correctly in SAS Procedures

Data not merging correctly

a079011 — Sun, 30 Nov 2014 16:31:42 GMT

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!

Re: Data not merging correctly

Haikuo — Sun, 30 Nov 2014 17:04:32 GMT

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

Re: Data not merging correctly

a079011 — Sun, 30 Nov 2014 23:56:15 GMT

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