topic Re: vis and date mismatch in SAS Programming

vis and date mismatch

eric2 — Mon, 03 Jul 2017 03:19:08 GMT

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

Re: vis and date mismatch

art297 — Mon, 03 Jul 2017 03:46:05 GMT

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

Re: vis and date mismatch

eric2 — Mon, 03 Jul 2017 04:12:02 GMT

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;

Re: vis and date mismatch

eric2 — Mon, 03 Jul 2017 04:42:37 GMT

ignore my prev post. i used full join and seems it worked. Thanks for your help

Re: vis and date mismatch

art297 — Mon, 03 Jul 2017 04:46:30 GMT

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

Re: vis and date mismatch

eric2 — Mon, 03 Jul 2017 19:14:52 GMT

Thanks Art.