Help using Base SAS procedures

Matching to another data set

Reply
Frequent Contributor
Posts: 126

Matching to another data set

I am wondering if I'm on the right track with this part below:

 

I need ALL of the fields from claims to come in and add to the original csv file, matching on clmbaseid.  This is what I have so far....

OPTIONS NODATE LS=132 OBS=MAX SORT=2430 SORTPGM=BEST            
         PS=59 NOBYLINE YEARCUTOFF=1920;                         
                                                                 
  **COMBINE NINST2 AND INST2**                                   
                                                                 
  PROC SORT DATA=NINST.DATA  OUT=NINST2.DATA;                    
     BY CLMBASEID;                                               
  RUN;                                                           
                                                                 
  PROC SORT DATA=INST.DATA  OUT=INST2.DATA;                      
     BY CLMBASEID;                                               
  RUN;                                                           
                                                                 
  DATA CLMWH.DATA;                                               
   SET NINST2                                                    
       INST2;                                                    
   BY CLMBASEID;                                                 
  RUN;                                                           
                                                                 
  PROC SORT DATA=CLMWH.DATA OUT=CLMWH2.DATA; BY CLMBASEID; RUN;  
                                                                 
  PROC SORT DATA=PROJECT.DATA OUT=FINPROJ.DATA BY CLMBASEID; RUN;
                                                                 
  **MERGE CLMWH2 AND FINPROJ**                                   
  DATA CLMPROJ;                                        
   MERGE CLMWH2.DATA(IN=A)                             
         FINPROJ.DATA (IN=B);                          
   BY CLMBASEID;                                       
   IF A AND B;                                         
   RUN;                                                
                                                       
  PROC SQL                                             
   CREATE TABLE REC1.DATA AS                           
    SELECT ALL                                         
     FROM CLMWH.DATA AS A INNERJOIN CLMPROJ.DATA AS B  
       ON A.CLMBASEID=B.CLMBASEID; QUIT;  

 

Super User
Posts: 3,909

Re: Matching to another data set

If you need all claims then change this step to see if it helps. Note I've changed IF A AND B to just IF A. That will keep all rows from your first table.

 

DATA CLMPROJ;                                        
   MERGE CLMWH2.DATA(IN=A)                             
         FINPROJ.DATA (IN=B);                          
   BY CLMBASEID;                                       
   IF A;                                         
   RUN;   
Frequent Contributor
Posts: 126

Re: Matching to another data set

Thank you. Just to clarify, the FINPROJ.DATA is the csv file and the CLMWH2.DATA is the claims database where I need additional information added to the csv file. 

 

So would it be IF B instead?

Super User
Posts: 3,909

Re: Matching to another data set

[ Edited ]

Your requirements aren't clear to me. You said you wanted all claims, which the - IF A - will give you. If this is still not giving you what you want then by all means try IF B. This will give you all FINPROJ rows instead.

Frequent Contributor
Posts: 126

Re: Matching to another data set

All the rows or columns? I need all the columns from A added to B.
Super User
Posts: 3,909

Re: Matching to another data set

The MERGE will give you all columns from both tables. What is it about your current code that is not giving you what you want?

Ask a Question
Discussion stats
  • 5 replies
  • 119 views
  • 1 like
  • 2 in conversation