topic Re: Using first. and last. to determine sections of data to remove or keep in SAS Programming

Using first. and last. to determine sections of data to remove or keep

dwsmith — Thu, 10 Mar 2016 20:42:16 GMT

data test;
     format dt date9;
     format dt2 date9;
     input dt dt2;
     datalines;
     20000 20001
     20000 20002
     20000 20003
     21000 21001
     21000 21002
     21000 21003
     21000 21004
     21000 21005
     ;
run;

proc sort data = test;
     by dt dt2;
run;

data check;
    set test;
    by dt dt2;
    if last.dt = first.dt then
         if abs(last.dt2 - first.dt) < 5 then delete;
run;

What I would like to happen is the section of

 20000 20001
 20000 20002
 20000 20003

dates to all be deleted and the next section

  21000 21001
  21000 21002
  21000 21003
  21000 21004
  21000 21005

to all be retained. How can I get this to work?

Re: Using first. and last. to determine sections of data to remove or keep

DanZ — Thu, 10 Mar 2016 20:55:11 GMT

You can assign a counter variable, and limit the output to the first group of dt.

data check;
    set test;
    by dt dt2;
    format dt dt2 mmddyy10.;
	if first.dt then counter+1;
	if counter=2;
run;

Re: Using first. and last. to determine sections of data to remove or keep

dwsmith — Thu, 10 Mar 2016 20:53:32 GMT

Where do I add that to my current set up? Or is this a new data step?

Re: Using first. and last. to determine sections of data to remove or keep

DanZ — Thu, 10 Mar 2016 20:57:30 GMT

Oh, sorry. I should have put if counter = 2. Replace your check datastep.

Re: Using first. and last. to determine sections of data to remove or keep

Kurt_Bremser — Fri, 11 Mar 2016 08:19:38 GMT

This condition:

if last.dt = first.dt

can only be true when there is only one observation in the current BY (dt) group. When you process the last observation of a by group containing multiple observations, only last. is true, and in the next observation only first. will be true.

Re: Using first. and last. to determine sections of data to remove or keep

dwsmith — Fri, 11 Mar 2016 12:49:51 GMT

Your suggestion doesn't yield the desired outcome. This is also a toy problem so we won't have the luxury of saying counter occurrence # is desired.

Re: Using first. and last. to determine sections of data to remove or keep

dwsmith — Fri, 11 Mar 2016 12:32:17 GMT

How can I do what I am looking to do though?

Re: Using first. and last. to determine sections of data to remove or keep

dwsmith — Fri, 11 Mar 2016 13:26:45 GMT

Here is a solution I have that worked now:

data check;
    set test;
    by dt dt2;
	format dt dt2 date9.;
	diff = abs(dt - dt2);
	if diff < 5 then delete;
	if last.dt then output;
run;