how do i keep the latest date possible of that year. (rows)

[aarony]

i have

datadate

20040101

20040102

and so on until 20131231.

I basically wan to keep rows with the last date of the year, for each year

there are some caveats:

some years last day with the data might be 20131229 for example not 20131231.

i wan to end up with

20041231

20051231

20061231 and so on and if 31st is not observable the latest date possible of that year.

[slchen]

data have;

input date yymmdd8.;

format date yymmddn8.;

cards;

20040102

20040312

20040616

20041231

20060419

20061229

20080223

20081231

;

run;

proc sql;

  select max(date) as date format=yymmddn8. from have group by year(date);

  quit;

[aarony]

sorry i should have been more specific

i have company names as well for this.

so basically

datadate

20040101

20040102

and so on until 20131231.

for companies a to z.

i want to keep the last date of each year for each company.

how may i tackle this issue? thank you in advance.

[Reeza · Accepted solution]

1. convert your field to an actual date field, slchen has posted a method for this above.

2. Create a year variable

3. Sort by Date/Year

4. Use last. processing to get the last one.

data part1;

set have;

date_var=input(year, yymmdd8.);

format date_var date9.;

year=year(date_var);

run;

proc sort data= part1;

by company year date;

run;

data want;

set have;

by company year;

if last.year;

run;

[aarony]

you are a magician reeza. thank you!

[Ksharp]

data have;
input date yymmdd8.;
format date yymmddn8.;
cards;
20040102
20040312
20040616
20041231
20060419
20061229
20080223
20081231
;
run;
 data last;
  set have;
  if date=mdy(1,1,year(date)+1)-1;
run;

Xia Keshan