topic Re: code remove dup based on different variables in SAS Programming

code remove dup based on different variables

Smitha9 — Fri, 26 May 2023 14:33:41 GMT

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

Re: code remove dup based on different variables

Patrick — Sat, 27 May 2023 02:15:56 GMT

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;

Re: code remove dup based on different variables

Ksharp — Sat, 27 May 2023 09:02:22 GMT

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;

Re: code remove dup based on different variables

mkeintz — Sun, 28 May 2023 02:26:08 GMT

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;