Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

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

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.


Accepted Solutions
Solution
‎04-23-2014 09:48 PM
Super User
Posts: 19,869

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

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;

View solution in original post


All Replies
Super Contributor
Posts: 275

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

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;

Frequent Contributor
Posts: 102

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

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.

Solution
‎04-23-2014 09:48 PM
Super User
Posts: 19,869

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

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;

Frequent Contributor
Posts: 102

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

you are a magician reeza. thank you!

Super User
Posts: 10,044

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

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 355 views
  • 1 like
  • 4 in conversation