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
Obs | Protocol_No | visit_status | Workflow_Status | protocol | protocol1 | ||
1 | A3921104 | Locked | Complete | 1104 | 1104 | ||
2 | A3921104 | Locked | Complete | 1104 | 1104 | ||
Obs | Site_Number | Category | protocol | ||||
1 | A3921104 1079 | Other | 1104 | ||||
2 | A3921104 1079 | Other | 1104 | ||||
3 | A3921104 1079 | Other | 1104 | ||||
4 | A3921104 1079 | Other | 1104 | ||||
5 | A3921104 1079 | Other | 1104 | ||||
6 | A3921104 1134 | Other | 1104 | ||||
7 | A3921104 1134 | Other | 1104 | ||||
8 | A3921104 1134 | Other | 1104 | ||||
Obs | Site_Number | Category | protocol | Protocol_No | visit_status | Workflow_Status | protocol1 |
1 | A3921104 1079 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
2 | A3921104 1079 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
3 | A3921104 1079 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
4 | A3921104 1079 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
5 | A3921104 1079 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
6 | A3921104 1134 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
7 | A3921104 1134 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
8 | A3921104 1134 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
this the my aimed result
Obs | Site_Number | Category | protocol | Protocol_No | visit_status | Workflow_Status | protocol1 |
1 | A3921104 1079 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
2 | A3921104 1079 | Other | 1104 | A3921104 | Locked | Complete | 1104 |
3 | A3921104 1079 | Other | 1104 | . | . | . | . |
4 | A3921104 1079 | Other | 1104 | . | . | . | . |
5 | A3921104 1079 | Other | 1104 | . | . | . | . |
6 | A3921104 1134 | Other | 1104 | . | . | . | . |
7 | A3921104 1134 | Other | 1104 | . | . | . | . |
8 | A3921104 1134 | Other | 1104 | . | . | . | . |
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;
By what logic are you making the values missing even they have matching id's for a merge.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.