vis and date mismatch

Reply
New Contributor
Posts: 4

vis and date mismatch

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

PROC Star
Posts: 7,356

Re: vis and date mismatch

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

 

New Contributor
Posts: 4

Re: vis and date mismatch

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;

New Contributor
Posts: 4

Re: vis and date mismatch

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

PROC Star
Posts: 7,356

Re: vis and date mismatch

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

New Contributor
Posts: 4

Re: vis and date mismatch

Thanks Art.

Ask a Question
Discussion stats
  • 5 replies
  • 139 views
  • 0 likes
  • 2 in conversation