Solved
Contributor
Posts: 21

# Identifying unique date?

I have an example of the following data:

ID          date                    year

D1          01MAR2007     2007

E2          04MAR2007     2007

D5          04MAR2007     2007

C9          07MAR2007     2007

E6          10MAR2007     2007

etc..

B6          11MAR2008     2008

B6          11MAR2008     2008

D1          11MAR2008     2008

E2          13MAR2008     2008

C9          13MAR2008     2008

B3          14MAR2008     2008

B4          17MAR2008     2008

etc.

repeat for multiple years

I would like to identify the third unique date for each year. For 2007 this is 07MAR2007. For 2008 this is 14MAR2008. I have multiple years of data but have only shown two.

Thanks for any suggestions.

Accepted Solutions
Solution
‎12-11-2012 11:45 AM
PROC Star
Posts: 8,167

## Re: Identifying unique date?

Here is a proc sort/proc transpose/data step approach:

data have;

informat date date9.;

format date date9.;

input ID \$ date  year;

cards;

D1          01MAR2007     2007

E2          04MAR2007     2007

D5          04MAR2007     2007

C9          07MAR2007     2007

E6          10MAR2007     2007

B6          11MAR2008     2008

B6          11MAR2008     2008

D1          11MAR2008     2008

E2          13MAR2008     2008

C9          13MAR2008     2008

B3          14MAR2008     2008

B4          17MAR2008     2008

;

proc sort data=have (keep=date year) out=want nodupkey;

by year date;

run;

proc transpose data=want out=want;

by year;

run;

data want (drop=_: col;

format date date9.;

set want;

date=smallest(3,of col;

run;

All Replies
Posts: 3,061

## Re: Identifying unique date?

Assuming these DATE values are truly (numeric) date values, and not character strings, then I think PROC SQL will get you there

PROC SQL;

create table unique_dates as select distinct date,year from whatever order by date;

quit;

Then, use a data step to find the third value in each year.

I guess the whole thing could also be done in a single data step, if you want to do a little more programming.

--
Paige Miller
Solution
‎12-11-2012 11:45 AM
PROC Star
Posts: 8,167

## Re: Identifying unique date?

Here is a proc sort/proc transpose/data step approach:

data have;

informat date date9.;

format date date9.;

input ID \$ date  year;

cards;

D1          01MAR2007     2007

E2          04MAR2007     2007

D5          04MAR2007     2007

C9          07MAR2007     2007

E6          10MAR2007     2007

B6          11MAR2008     2008

B6          11MAR2008     2008

D1          11MAR2008     2008

E2          13MAR2008     2008

C9          13MAR2008     2008

B3          14MAR2008     2008

B4          17MAR2008     2008

;

proc sort data=have (keep=date year) out=want nodupkey;

by year date;

run;

proc transpose data=want out=want;

by year;

run;

data want (drop=_: col;

format date date9.;

set want;

date=smallest(3,of col;

run;

Super User
Posts: 6,785

## Re: Identifying unique date?

Perhaps the simplest approach:

data want;

set have;

by year date;

if first.year then counter=0;

if first.date then do;

counter + 1;

if counter=3 then output;

end;

run;

Good luck.

Contributor
Posts: 21

## Re: Identifying unique date?

Thanks. This worked too. I added the following after to give just one date for each year.

proc sort data=have (keep = date year) out= have2 nodupkey;

by year date;

run;

Super User
Posts: 6,785

## Re: Identifying unique date?

Good catch.  I thought about that over lunch, and made a minor change to the code.  So the currently-posted version won't have to sort afterwards.

Contributor
Posts: 21