BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
a079011
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

2 REPLIES 2
Haikuo
Onyx | Level 15

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 973 views
  • 3 likes
  • 2 in conversation