topic Would the Retain function help? in SAS Programming

Would the Retain function help?

cadams47 — Mon, 21 Nov 2016 15:04:54 GMT

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?

Re: Would the Retain function help?

Kurt_Bremser — Mon, 21 Nov 2016 15:09:53 GMT

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;

Re: Would the Retain function help?

cadams47 — Mon, 21 Nov 2016 15:11:48 GMT

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?

Re: Would the Retain function help?

Kurt_Bremser — Mon, 21 Nov 2016 15:16:37 GMT

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;

Re: Would the Retain function help?

cadams47 — Mon, 21 Nov 2016 15:21:18 GMT

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.

Re: Would the Retain function help?

Kurt_Bremser — Mon, 21 Nov 2016 15:36:49 GMT

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

Re: Would the Retain function help?

mkeintz — Mon, 21 Nov 2016 15:46:23 GMT

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

Re: Would the Retain function help?

cadams47 — Mon, 21 Nov 2016 17:53:01 GMT

Thank you everyone!

Re: Would the Retain function help?

Kurt_Bremser — Tue, 22 Nov 2016 07:38:27 GMT

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

Re: Would the Retain function help?

cadams47 — Tue, 22 Nov 2016 12:04:29 GMT

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