DATA Step, Macro, Functions and more

merging data sets

Reply
Frequent Contributor
Posts: 132

merging data sets

I'm trying to merge two data sets and I can't get the result that I want 

 

this is my code 

proc sort data=vwr2 out=vwr3; by protocol protocol1; run;
proc sort data=pqr2 out=pqr3; by protocol; run;

 

data mmm;
merge pqr3 vwr3;
by protocol;
run;

 

this is the two datasets and the result

ObsProtocol_Novisit_statusWorkflow_Statusprotocolprotocol1  
1A3921104LockedComplete11041104  
2A3921104LockedComplete11041104  
        
ObsSite_NumberCategoryprotocol    
1A3921104 1079Other1104    
2A3921104 1079Other1104    
3A3921104 1079Other1104    
4A3921104 1079Other1104    
5A3921104 1079Other1104    
6A3921104 1134Other1104    
7A3921104 1134Other1104    
8A3921104 1134Other1104    
        
        
        
ObsSite_NumberCategoryprotocolProtocol_Novisit_statusWorkflow_Statusprotocol1
1A3921104 1079Other1104A3921104LockedComplete1104
2A3921104 1079Other1104A3921104LockedComplete1104
3A3921104 1079Other1104A3921104LockedComplete1104
4A3921104 1079Other1104A3921104LockedComplete1104
5A3921104 1079Other1104A3921104LockedComplete1104
6A3921104 1134Other1104A3921104LockedComplete1104
7A3921104 1134Other1104A3921104LockedComplete1104
8A3921104 1134Other1104A3921104LockedComplete1104

 

this the my aimed result 

ObsSite_NumberCategoryprotocolProtocol_Novisit_statusWorkflow_Statusprotocol1
1A3921104 1079Other1104A3921104LockedComplete1104
2A3921104 1079Other1104A3921104LockedComplete1104
3A3921104 1079Other1104....
4A3921104 1079Other1104....
5A3921104 1079Other1104....
6A3921104 1134Other1104....
7A3921104 1134Other1104....
8A3921104 1134Other1104....

 

Super User
Posts: 6,543

Re: merging data sets

While I'm not sure I can justify your intended result, you want what you want.  So here's an easy way:

 

data mmm;
merge pqr3 vwr3;
by protocol;

output;

call missing(Protocol_No);

call missing(visit_status);

call missing(Workflow_Status);

call missing(protocol1);
run;

Super Contributor
Posts: 478

Re: merging data sets

By what logic are you making the values missing even they have matching id's for a merge.

Thanks,
Suryakiran
Super User
Posts: 10,626

Re: merging data sets

data mmm;
inb=0;
merge pqr3 vwr3(in=inb rename=(protocol=protocol1));
by protocol;
if not inb then call missing(visit_status,workflow,protocol1);
run;
Ask a Question
Discussion stats
  • 3 replies
  • 93 views
  • 0 likes
  • 4 in conversation