topic Re: retain the second date for each id in SAS Programming

retain the second date for each id

lillymaginta — Tue, 10 Mar 2020 15:31:33 GMT

data test;
input id$2. start;
attrib start format =date9. informat=date9.;

datalines;
1 01JAN2015 
1 18FEB2015 
2 01jan2015 
2 01apr2015 
3 01JAN2015 
3 01JAN2015 
;

run;

I want to retain the second date for each id: output data:

1 18FEB2015

2 01apr2015

3 01JAN2015

Re: retain the second date for each id

novinosrin — Tue, 10 Mar 2020 15:29:56 GMT

Are you sure of the results for

1 01JAN2015

1 18FEB2015

data test;
input id$2. start;
attrib start format =date9. informat=date9.;

datalines;
1 01JAN2015 
1 18FEB2015 
2 01jan2015 
2 01apr2015 
3 01JAN2015 
3 01JAN2015 
;
run;

data want;
 do _n_=1 by 1 until(last.id);
  set test;
  by id;
  if _n_=2 then output;
 end;
run;

Re: retain the second date for each id

novinosrin — Tue, 10 Mar 2020 15:31:18 GMT

To be safe, in an event of having only 1 record per id

data test;
input id$2. start;
attrib start format =date9. informat=date9.;

datalines;
1 01JAN2015 
1 18FEB2015 
2 01jan2015 
2 01apr2015 
3 01JAN2015 
3 01JAN2015 
;
run;

data want;
 do _n_=1 by 1 until(last.id);
  set test;
  by id;
  if first.id and last.id then output;
  else if _n_=2 then output;
 end;
run;

Re: retain the second date for each id

mkeintz — Tue, 10 Mar 2020 15:41:55 GMT

You've already chosen @novinosrin's solution. As a slight conceptual change (from explicit loop to the hidden implicit loop), consider this analog:

data want;
  set have;
  by id;
  if first.id=0  and lag(first.id)=1;
run;

However, this approach is not as neatly extensible to higher order dates. Here's what it would look like for say, the 4th date of each id:

data want;
  set have;
  by id;
  if max(first.id,lag(first.id),lag2(first.id))=0 and lag3(first.id)=1;
run;

Re: retain the second date for each id

ballardw — Tue, 10 Mar 2020 22:12:37 GMT

@lillymaginta wrote:
data test;
input id$2. start;
attrib start format =date9. informat=date9.;

datalines;
1 01JAN2015 
1 18FEB2015 
2 01jan2015 
2 01apr2015 
3 01JAN2015 
3 01JAN2015 
;

run;
I want to retain the second date for each id: output data:

1 18FEB2015

2 01apr2015

3 01JAN2015

Are you absolutely positive that there are never more than 2 records for each id?

Are there other variables that have to be brought along? If not perhaps:

proc summary data=have nway;
   class id;
   var  start;
   output out=want (drop=_:) max=;
run;

which may also handle the issue of more than 2 records assuming the actual latest date value is desired and not order position.