Select end month dates from daily data for each company

[OS2Rules]

I think that is the best I can do.  I was thinking of sorting by day/month/hear then taking the last.day, but that will only

give you 1 record.

I think the merge is the way to go.

[Reeza · Accepted solution]

This is a two pass solution, but in one step. A 4GB file will process fine unless you need it back in 2 secs. 

data have;

informat days ddmmyy10. company $8. ;format days date9.;

input  Days         Company ;

cards;

27/07/2000       Firm1

28/07/2000       Firm1

29/07/2000       Firm1

30/07/2000       Firm1

31/07/2000       Firm1

27/08/2000       Firm1

28/08/2000       Firm1

29/08/2000       Firm1

30/08/2000       Firm1

30/08/2000       Firm1

30/08/2000       Firm1

27/07/2000       Firm2

28/07/2000       Firm2

29/07/2000       Firm2

30/07/2000       Firm2

31/07/2000       Firm2

27/08/2000       Firm2

28/08/2000       Firm2

29/08/2000       Firm2

30/08/2000       Firm2

30/08/2000       Firm2

31/08/2000       Firm2

31/08/2000       Firm2

;

run;

proc sql;

  create table want as

  select company, days, month(days) as month

  from have

  group by company, calculated month

  having days=max(days);

quit;

[Ruslan]

Thanks a lot, Reeza! That works perfectly.

Just quick question/clarification:

I have other variables (about 12). Can I include these variables by typing "select var1 var2 var3 company, days, month(days) as month..."? or is there any other way?

Huge thanks again. Look forward to hearing from you soon.

[Reeza]

Try it and let me know

[Ruslan]

That works perfectly as well. Many thanks, Reeza, for your help.

One minor worry:

When I run proc sql, at the end I got this message: Note: The query requires remerging summary statistics back with the original data. Can this note cause any problems whatsoever? Any missing data?

[Reeza]

No, this is telling you that it is a two pass solution and merging but it is doing it for you automatically.

[Ruslan]

That's great! I hugely appreciate your help

Kind regards.

Ruslan