topic Re: dedeplicating on most recent date in SAS Procedures

dedeplicating on most recent date

deleted_user — Fri, 18 Dec 2009 19:42:00 GMT

How do you deduplicate based on most recent date?

PROC SORT data=total;
by week ID;
run;
PROC FREQ DATA = total noprint;
by week;
table ID/ out = ID_DUPS (keep = week ID Count where = (Count > 1)) ;
run;
PROC PRINT DATA = ID_DUPS;
run;

Re: dedeplicating on most recent date

Flip — Fri, 18 Dec 2009 19:49:14 GMT

for the last week within an ID use:

PROC SORT data=total;
by ID week ;
run;

data total;
set total;
by ID week;
if last.ID;
run;

Re: dedeplicating on most recent date

deleted_user — Fri, 18 Dec 2009 20:19:25 GMT

Thanks. How do you deduplicate selecting for the latest date within a week? There's a unique time/date stamp field within each week.

Re: dedeplicating on most recent date

sbb — Fri, 18 Dec 2009 20:24:12 GMT

Separate the DATEPART (it's a function in SAS) to create a SAS DATE variable and use it with your BY processing.

Scott Barry
SBBWorks, Inc.

Re: dedeplicating on most recent date

deleted_user — Fri, 18 Dec 2009 20:28:54 GMT

Thanks. Once I get SAS to create a SAS date, how do I ask SAS to identify the most recent date and select for it?

Re: dedeplicating on most recent date

sbb — Fri, 18 Dec 2009 21:45:22 GMT

Refer to your other related post for info on BY GROUP processing.

Scott

Re: dedeplicating on most recent date

deleted_user — Wed, 30 Dec 2009 19:48:24 GMT

Thanks!

Re: dedeplicating on most recent date

deleted_user — Wed, 30 Dec 2009 21:52:58 GMT

Consolidated from related post:

You have a couple of options - one is to perform two sorts with ay BY ID DESCENDING DATE; on the first sort and then followed with a SORT NODUPKEY EQUALS and a BY ID; statement.

The other option is to again sort with ID DESCENDING DATE, and then use a SAS DATA step with a SET and a BY ID; statement -- and use BY GROUP processing with IF FIRST.ID processing to subset your input and only capture the first occurence of ID values.

Scott Barry

Suggested Google advanced search argument on this topic:

data step by group processing site:sas.com

Re: dedeplicating on most recent date

ballardw — Sat, 09 Jan 2010 00:08:54 GMT

> Thanks. How do you deduplicate selecting for the
> latest date within a week? There's a unique
> time/date stamp field within each week.

Depending on how you're using this:

Proc summary data=(input data) nway;
class id week;
var (variable with date/time stamp as a SAS datetime variable);
output out=(output data set name) max=;
run;

May want to have option (drop= _type_ _freq_) with the output data. The output dataset will have the largest date/time within each combination of ID and WEEK. As a side effect the output data will be sorted by ID and week but no sort is needed beforehand.

Re: dedeplicating on most recent date

Patrick — Sat, 09 Jan 2010 08:27:59 GMT

I hope this example code will point you into the right direction.

The main idea is to use intnx() to align your datestamps to the date of the last day of the week the datestamp value is from (Sunday) and use this additional variable "LastDayInWeek" for sorting.

data have;
id=1;
format DT datetime20.;
DT='19Dec2009 00:00:00'dt;
do while (DT lt '05Jan2010 00:00:00'dt);
output;
DT=sum(DT,ranuni(1)*86400);
end;
run;

proc sql;
create view VSortedByIDAndWeekAndDate as
select *, datepart(intnx('dtweek.2',DT,0,'E')) format=weekdatx. as LastDayInWeek
from have
order by id,LastDayInWeek,DT
;
quit;

data want;
set VSortedByIDAndWeekAndDate;
by id LastDayInWeek DT;
if last.LastDayInWeek then output;
run;

proc print data=want;
run;

HTH
Patrick