BookmarkSubscribeRSS Feed
Smitha9
Fluorite | Level 6

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

3 REPLIES 3
Patrick
Opal | Level 21

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;

Patrick_0-1685153751583.png

 

Ksharp
Super User
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
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 729 views
  • 2 likes
  • 4 in conversation