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
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.