topic Re: How to keep only first date by ID with multiple rows with same date. in SAS Programming

How to keep only first date by ID with multiple rows with same date.

Manhort — Sun, 21 Aug 2022 00:52:45 GMT

Hi everyone,

I have sample data below. I need to keep all the rows that have the first date for each ID. For example, ID 2 I want to keep both rows that have '01/25/2016' and ditch the other 2. My full data have any number of rows so this needs a general solution.

Any help is appreciated. Thanks!

DATA have;
input id date mmddyy10. var1 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 A
1 02/25/2016 B
2 01/25/2016 A
2 01/25/2016 B
2 01/29/2016 B
2 02/04/2016 A
3 03/10/2016 A
3 03/10/2016 A
3 03/10/2016 B
3 03/22/2016 B

;
RUN;

DATA want;
input id date mmddyy10. var1 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 A
2 01/25/2016 A
2 01/25/2016 B
3 03/10/2016 A
3 03/10/2016 A
3 03/10/2016 B

RUN;

Re: How to keep only first date by ID with multiple rows with same date.

ballardw — Sun, 21 Aug 2022 04:08:43 GMT

One way:

data want;
   set have;
   by id;
   retain fdate;
   if first.id then do;
      fdate=date;
      output;
   end;
   else if date=fdate then output;
   drop fdate;
run;

BY group creates variables first. and last. data that are numeric 1/0 which can be used to test if the current record is the first or last of a group for each variable on the BY statement. The values are accessed using First.variablename or Last.variablename, pay attention to that dot, it is critical. So when the first value of ID for a group is encountered store the value of DATE into a Retained variable, Fdate in this case, that will persist across data step boundaries.

The explicit Output writes the First record for the ID and the Output in the Else clause only writes output to the set when the value of Date matches that of the retained value.

General hint: If your problem has anything to do with "first" anything than likely BY group processing with the First. or Last. variables

Re: How to keep only first date by ID with multiple rows with same date.

PeterClemmensen — Sun, 21 Aug 2022 06:11:21 GMT

proc sql;
   create table want as
   select * from have
   group by id
   having date = min(date)
   ;
quit;

Re: How to keep only first date by ID with multiple rows with same date.

Kurt_Bremser — Sun, 21 Aug 2022 06:55:49 GMT

You can simplify this.

data want;
   set have;
   by id;
   retain fdate;
   if first.id then fdate=date;
   if date=fdate;
   drop fdate;
run;

Re: How to keep only first date by ID with multiple rows with same date.

Ksharp — Sun, 21 Aug 2022 10:43:56 GMT

DATA have;
input id date mmddyy10. var1 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 A
1 02/25/2016 B
2 01/25/2016 A
2 01/25/2016 B
2 01/29/2016 B
2 02/04/2016 A
3 03/10/2016 A
3 03/10/2016 A
3 03/10/2016 B
3 03/22/2016 B

;
data want;
 set have;
 by id date notsorted;
 if first.id then n=0;
 n+first.date;
 if n=1;
drop n;
run;