DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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

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?


Accepted Solutions
Solution
‎03-11-2016 08:26 AM
Contributor
Posts: 40

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

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;

View solution in original post


All Replies
Contributor
Posts: 38

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

[ Edited ]

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;
Contributor
Posts: 40

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

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

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

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

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

[ Edited ]

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.

Super User
Posts: 7,778

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 40

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

Posted in reply to KurtBremser

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

Solution
‎03-11-2016 08:26 AM
Contributor
Posts: 40

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

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 336 views
  • 0 likes
  • 3 in conversation