topic Re: how do i keep the latest date possible of that year. (rows) in SAS Procedures

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

aarony — Thu, 24 Apr 2014 00:46:29 GMT

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.

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

slchen — Thu, 24 Apr 2014 01:14:16 GMT

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;

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

aarony — Thu, 24 Apr 2014 01:21:13 GMT

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.

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

Reeza — Thu, 24 Apr 2014 01:48:02 GMT

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;

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

aarony — Thu, 24 Apr 2014 02:32:23 GMT

you are a magician reeza. thank you!

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

Ksharp — Thu, 24 Apr 2014 03:28:56 GMT

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