code remove dup based on different variables

[Smitha9]

ID        Type       Date                              visit

1          New        01/23/2023                314566

1          New          03/23/2023              102345

1          New          02/12/2022                314566

1         return         04/05/2023             102345

1       return            05/01/2023            102345

the above data should have only one New. I want to select only one New from the visit number which is similar to return visit number. And delete the rest of New which has different visit number.

I want to do the code for the large dataset.

output example:

ID           Type                          Date                            visit

1              New                    03/23/2023                  102345

1              return                 04/05/2023                  102345

1              return                 05/01/2023                   102345

[Patrick]

Below one option.

data have;
  input ID Type $ Date:mmddyy10. visit_no;
  format date date9.;
  datalines;
1 New 01/23/2023 314566
1 New 03/23/2023 102345
1 New 02/12/2022 314566
1 return 04/05/2023 102345
1 return 05/01/2023 102345
;

data want;
  if _n_=1 then 
    do;
      dcl hash h1(dataset:'have(where=(type="return"))');
      h1.defineKey('visit_no');
      h1.defineDone();
    end;
  set have;
  if h1.check()=0;
run;
proc print data=want;
run;

[Ksharp]

data have;
  input ID Type $ Date:mmddyy10. visit_no;
  format date date9.;
  datalines;
1 New 01/23/2023 314566
1 New 03/23/2023 102345
1 New 02/12/2022 314566
1 return 04/05/2023 102345
1 return 05/01/2023 102345
;

proc sql;
create table want as
select * from have
 group by id,visit_no
  having count(distinct type)>1
   order by 1,2,3;
quit;

[mkeintz]

If your data are sorted by ID, and there is only one desired visit_no among the type="return" observations, then a self-merge works:

data have;
  input ID Type $ Date:mmddyy10. visit_no;
  format date date9.;
  datalines;
1 New 01/23/2023 314566
1 New 03/23/2023 102345
1 New 02/12/2022 314566
1 return 04/05/2023 102345
1 return 05/01/2023 102345
run;

data want (drop=_:);
  merge have (where=(type='return') rename=(visit_no=_visit_no))
        have;
  by id;
  if visit_no=_visit_no;
run;