topic Re: How to keep the first sample of the same indivual in New SAS User

How to keep the first sample of the same indivual

GK5 — Fri, 06 Dec 2019 13:43:00 GMT

Hi all SAS experts!

My dataset looks something like this:

Unique ID Bacteria date:

110 1 s.aureus 1/12 2000

111 1 e.coli 1/12 2000

112 1 e.coli 1/9 2000

113 1 strep 3/10 2004

114 2 s.aureus 1/3 2010

115 2 e.coli 5/3 2010

116 2 pseudomonas 7/3 2010

170 3 pseudomonas 5/10 2005

175 3 e.coli 5/10 2005

176 3 enterobac 5/10 2005

180 3 s.aureus 1/10 2006

190 3 s.aureus 6/12 2006

How can I make SAS to select the first observation from the same ID of the same date only? With this example I would like to for an example keep for ID=1 the unique variable 110 and 111 as they are from the same date but get rid of the other samples with the same ID=1.

Hope you guys can help!

Kind regards

Re: How to keep the first sample of the same indivual

PeterClemmensen — Fri, 06 Dec 2019 13:58:30 GMT

@GK5 Hi and welcome to the SAS Community 🙂

Here is one way

data have;
input Unique ID Bacteria :$20. date :ddmmyy10.;
format date ddmmyy10.;
datalines;
110 1 s.aureus 1/12/2000
111 1 e.coli 1/12/2000 
112 1 e.coli 1/9/2000
113 1 strep 3/10/2004
114 2 s.aureus 1/3/2010
115 2 e.coli 5/3/2010
116 2 pseudomonas 7/3/2010
170 3 pseudomonas 5/10/2005 
175 3 e.coli 5/10/2005
176 3 enterobac 5/10/2005 
180 3 s.aureus 1/10/2006
190 3 s.aureus 6/12/2006
;

data want;
    array _ {99999} _temporary_;
    set have;
    by id;
    if first.id then _[id]=date;
    if _[id]=date;
run;

Result:

Unique ID Bacteria     date 
110    1  s.aureus     01/12/2000 
111    1  e.coli       01/12/2000 
114    2  s.aureus     01/03/2010 
170    3  pseudomonas  05/10/2005 
175    3  e.coli       05/10/2005 
176    3  enterobac    05/10/2005

Re: How to keep the first sample of the same indivual

GK5 — Fri, 06 Dec 2019 15:19:42 GMT

Thank you for your respons.

Appologies for not mentioning that this dataset is quite large - what I wrote is just an "sample" of the whole set to illustrate what my data looks like. So what should my code in SAS look like? Sorry, Rookie here 🙂

Re: How to keep the first sample of the same indivual

PeterClemmensen — Fri, 06 Dec 2019 15:22:15 GMT

How large? And what is your maximum value of ID?

Re: How to keep the first sample of the same indivual

GK5 — Fri, 06 Dec 2019 15:24:29 GMT

Max. value of ID in total is 4039199 - so it's a lot. 🙂

Re: How to keep the first sample of the same indivual

PeterClemmensen — Fri, 06 Dec 2019 15:29:33 GMT

My code can handle that, simply add more entries to the array like this

data want;
    array _ {9999999} _temporary_;
    set have;
    by id;
    if first.id then _[id]=date;
    if _[id]=date;
run;

Re: How to keep the first sample of the same indivual

Astounding — Fri, 06 Dec 2019 15:34:22 GMT

I think it's actually simpler than that:

data want;
   set have;
   by id notsorted;
   if first.id then _date = date;
   retain _date;
   if date = _date;
run;

Re: How to keep the first sample of the same indivual

RTN — Fri, 06 Dec 2019 18:28:03 GMT

I tried that but it only keeps the observations from the latest date not the earliest.

Re: How to keep the first sample of the same indivual

Astounding — Fri, 06 Dec 2019 18:46:24 GMT

Only you can see your program.

Only you can see your log.

Only you can see your data.

Only you can post something helpful to help explain why you are getting the last date instead of the first.