Would the Retain function help?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Would the Retain function help?

I have data that looks like this:

Obs    Date    CompanyID

1         7/15/15        1

2         12/31/01      2

3         12/31/02      2

4         3/31/05        3

5         5/15/05        3

 

What I need to do is keep the observations 1-4 and drop (or at least flag) observation 5. Observation 2 & 3 are not duplicates because there is a year in between those dates. Observations 4 and 5 will be treated as duplicates because they fall less than 60 days apart. 

Is the retain function appropriate?


Accepted Solutions
Solution
‎11-22-2016 10:53 AM
Super User
Posts: 7,431

Re: Would the Retain function help?

Use the lag() function:

proc sort data=have;
by date;
run;
/* just to be sure */

data want;
set have;
if date - lag(date) le 60 then delete;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎11-22-2016 10:53 AM
Super User
Posts: 7,431

Re: Would the Retain function help?

Use the lag() function:

proc sort data=have;
by date;
run;
/* just to be sure */

data want;
set have;
if date - lag(date) le 60 then delete;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: Would the Retain function help?

But does that take into account each company? A company might have one observation or it might have three. How can I make those decisions by company?

Super User
Posts: 7,431

Re: Would the Retain function help?

Just modify the sort and use by-processing in the data step:

proc sort data=have;
by company date;
run;

data want;
set have;
by company;
if date - lag(date) le 60 and not first.company then delete;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: Would the Retain function help?

I'm still hesitant because a company could have four observations, with two being close in date and two being 60+ days:

 

Date         CompanyID

5/15/05     4

6/30/05     4

6/30/09     4

6/30/10     4

 

In this scenario, if I relied on first.company, it would delete three of them. Only the first one would need to go.

Super User
Posts: 7,431

Re: Would the Retain function help?

[ Edited ]

It would keep three observations:

data have;
input date :mmddyy8. companyid;
format date mmddyy8.;
cards;
5/15/05 4
6/30/05 4
6/30/09 4
6/30/10 4
;
run;

data want;
set have;
by companyid;
if date - lag(date) le 60 and not first.companyid then delete;
run;

proc print noobs;
run;

Result:

    date    companyid

05/15/05        4    
06/30/09        4    
06/30/10        4    
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 947

Re: Would the Retain function help?

 

 

This program outputs all records in which the date > cutdate.  And cutdate is set to 60 days after the last "valid" date.  Except for the first case of company, when cutdate is set to missing (which is lower that all valid numeric values):

 

data want (drop=cutdate);
  retain cutdate;
   set have;
   by companyid;
   if first.companyid then cutdate=.;
   if date>cutdate;
   cutdate=date+60;
run;

 

regards,

Mark

Occasional Contributor
Posts: 7

Re: Would the Retain function help?

Thank you everyone!

Super User
Posts: 7,431

Re: Would the Retain function help?

Please mark the answer that worked for you as solution, for future reference.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: Would the Retain function help?

I will once I figure out which one is better. Both left me with another issue.

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 330 views
  • 2 likes
  • 3 in conversation