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