First, since you are new, some advice on your question.
It's great that you included sample data. When you do that, it's better to share the data as code, so that people can easily create your data. And when you share code, you should use the code format (see the running man icon). So to share your example data, you could share it like:
data have ;
input Color House Type : $12. Day Name :$8. ;
cards ;
2 3 garage 70 mickey
2 3 garage 71 minni
2 7 bungalow 15 nice
2 7 bungalow 16 bad
2 8 bungalow 3 cold
5 2 garage 7 10
5 80 bungalow 8 15
5 80 garage 9 8
;
Now, since you kindly went to the trouble of making sample data you have, and the example of the data you want, your question would be clearer if the example SQL code you posted actually used that sample data.
That's all tips for future questions.
As for my suggestion, I think you actually only need to group by 3 columns, not 4.
I think in most SQL implementations to get what you want would require joining together two subqueries, one to calculate the counts, and one to select the record with the max(Day). But because of an oddity in SAS SQL (where it will "remerge" data if you include a non-aggregated column in a grouped query), I think you can get what you want in one step:
proc sql ;
create table want as
select Color,House,Type,Day,Name,count(*) as Count
from have
group by Color,House,Type
having Day=max(day)
;
quit ;
If there are ties for max(day) that will return multiple rows.
... View more
