BookmarkSubscribeRSS Feed
mona4u
Lapis Lazuli | Level 10

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....

 

3 REPLIES 3
Astounding
PROC Star

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;

SuryaKiran
Meteorite | Level 14

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

Thanks,
Suryakiran
Ksharp
Super User
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 781 views
  • 0 likes
  • 4 in conversation