topic Re: Deleting all duplicates by date of first observation in SAS Programming

Deleting all duplicates by date of first observation

htht — Fri, 18 Sep 2020 07:32:19 GMT

Hi all,

I had this situation where I choose the onset date ranging from 01/01/2011 to 12/31/2011 and follow up until 12/31/2013. Therefore I need to exclude all the ID's with onset after 12/31/2011 on the first observation. For example, in the below scenario there are multiple observations for each ID. I want to delete all those ID's which had first onset occur after 12/31/2011.

Data I have

ID	Order of admission	Onset
1	1	02/09/2011
1	2	04/30/2012
1	3	08/12/2013
2	1	07/27/2011
3	1	05/11/2012
3	2	12/25/2012
4	1	11/09/2013
5	1	05/15/2011
5	2	08/22/2011

Data i want to keep

ID	Order of occurrence	Onset
1	1	02/09/2011
1	2	04/30/2012
1	3	08/12/2013
2	1	07/27/2011
5	1	05/15/2011
5	2	08/22/2011

I am a beginner of SAS and use SAS 9.4. Please help me. Thanks in advance for the support.

Re: Deleting all duplicates by date of first observation

Kurt_Bremser — Fri, 18 Sep 2020 07:41:12 GMT

data want;
set have;
by id;
retain _keep;
if first.id then _keep = (onset lt "31dec2011"d);
if _keep;
drop _keep;
run;

Untested, posted from my tablet; for tested code, supply example data in usable form (data step with datalines).

Re: Deleting all duplicates by date of first observation

htht — Fri, 18 Sep 2020 08:16:52 GMT

Dear KurtBremser,
I am so sorry for this. This is my datalines:

data have;
input ID Order_of_admission $onset: mmddyy10.;
format onset mmddyy10.;
cards;
1 1 02/09/2011
1 2 04/30/2012
1 3 08/12/2013
2 1 07/27/2011
3 1 05/11/2012
3 2 12/25/2012
4 1 11/09/2013
5 1 05/15/2011
5 2 08/22/2011
;
run;

Re: Deleting all duplicates by date of first observation

novinosrin — Fri, 18 Sep 2020 09:52:37 GMT


data have;
input ID Order_of_admission $onset: mmddyy10.;
format onset mmddyy10.;
cards;
1 1 02/09/2011
1 2 04/30/2012
1 3 08/12/2013
2 1 07/27/2011
3 1 05/11/2012
3 2 12/25/2012
4 1 11/09/2013
5 1 05/15/2011
5 2 08/22/2011
;
run;

proc sql;
create table want as
select *
from have
group by id	
having not min(onset)>'31dec2011'd
order by id, order_of_admission;
quit;

Re: Deleting all duplicates by date of first observation

Kurt_Bremser — Fri, 18 Sep 2020 10:57:01 GMT

Have you tried my code?

Re: Deleting all duplicates by date of first observation

htht — Fri, 18 Sep 2020 11:54:35 GMT

Hi KurtBremser,
Thank you so much. After class, I checked code in my real data and perfect.
You helped me to solve it.
Thank you.

Re: Deleting all duplicates by date of first observation

htht — Fri, 18 Sep 2020 11:56:30 GMT

I just change a little from "lt" to "le".
Thank you.