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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.