data have1;
input ID $ visitnum start_date :mmddyy10. ;
datalines;
101 1 8/26/2015
101 2 9/17/2015
101 3 10/2/2015
101 4 10/30/2015
101 5 11/24/2015
102 1 3/17/2016
102 2 3/24/2016
102 3 3/31/2016
102 4 4/28/2016
102 5 5/25/2016
;
run;
data have2;
input ID $ visitnum start_date :mmddyy10. ;
datalines;
101 1 8/26/2015
101 2 9/17/2015
101 22 10/2/2015
101 4 10/30/2015
101 5 11/24/2015
102 1 3/17/2016
102 2 3/24/2016
102 3 3/30/2016
102 4 4/28/2016
102 5 5/25/2016
;
run;
i need to flag subject 101 which has visit 22 mismatch since the dates are same, tehnically visitnum should be 2 for that date.
similary for 102 subject visitnum 3 has 3/30/2016 and have1 dataset has different date , so need to flag that record also. Any suggestions?
thx
eric
I would use a sql join and create two flags, one to indicate a casenum mismatch, and another to indicate a start_date mismatch. e.g.:
proc sql noprint; create table want as select a.*, b.visitnum as vnum2, b.start_date as sdate2, a.visitnum ne b.visitnum as vflag, a.start_date ne b.start_date as sflag from have1 a join have2 b on a.id eq b.id and (a.visitnum eq b.visitnum or a.start_date eq b.start_date) ; quit;
Art, CEO, AnalystFinder.com
Thanks Art.
does this join also work if we have a visit missing in head2 dataset but not in head1 dataset, like head2 has missing visitnum 6 for 102 subject
data have1;
input ID $ visitnum start_date :mmddyy10. ;
datalines;
101 1 8/26/2015
101 2 9/17/2015
101 3 10/2/2015
101 4 10/30/2015
101 5 11/24/2015
102 1 3/17/2016
102 2 3/24/2016
102 3 3/31/2016
102 4 4/28/2016
102 5 5/25/2016
102 6 6/20/2016
;
run;
data have2;
input ID $ visitnum start_date :mmddyy10. ;
datalines;
101 1 8/26/2015
101 2 9/17/2015
101 22 10/2/2015
101 4 10/30/2015
101 5 11/24/2015
102 1 3/17/2016
102 2 3/24/2016
102 3 3/30/2016
102 4 4/28/2016
102 5 5/25/2016
;
run;
ignore my prev post. i used full join and seems it worked. Thanks for your help
Yes, with full join it will do the extra task. However, you'll likely want to include the extra variables so that you can identify what was missing. e.g.:
proc sql noprint; create table want as select a.*, b.id as b_id, b.visitnum as b_visitnum, b.start_date as b_start_date, a.visitnum ne b.visitnum as vflag, a.start_date ne b.start_date as sflag from have1 a full join have2 b on a.id eq b.id and (a.visitnum eq b.visitnum or a.start_date eq b.start_date) ; quit;
Art, CEO, AnalystFinder.com
Thanks Art.
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.