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.
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;
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.
Try it and let me know
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?
No, this is telling you that it is a two pass solution and merging but it is doing it for you automatically.
That's great! I hugely appreciate your help
Kind regards.
Ruslan
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.