Identifying unique date?

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

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: 7,492

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=_: colSmiley Happy;

  format date date9.;

  set want;

  date=smallest(3,of colSmiley Happy;

run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,932

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.

Solution
‎12-11-2012 11:45 AM
PROC Star
Posts: 7,492

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=_: colSmiley Happy;

  format date date9.;

  set want;

  date=smallest(3,of colSmiley Happy;

run;

Super User
Posts: 5,516

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?

Posted in reply to Astounding

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: 5,516

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

Re: Identifying unique date?

Posted in reply to Astounding

Thanks!

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 253 views
  • 6 likes
  • 4 in conversation