How to make a table (with proc report or data step) of a grouped variable where in different columns are counts of different variables?

Question

Could you give some advise please how to calculate different counts to different columns when we group a certain variable with proc report (if it is possible with it)?

I copy here an example and the solution to better understand what i want to achieve. I can compile this table in sql in a way that i group them individually (with where statements, for example where Building_code = 'A') and then i join them to one table, but it is a little bit long, especially when I want to add more columns. Is there a way to define it in proc report or some shorter data step query, if yes can you give a short example please?

Example:

Solution:

Thank you for your time.

Answer 1

This should work. There is absolutely no need to do this by joining multiple tables.

data have;
    input Person_id Country :$15. Building_code $ Flat_code $ age_category $;
    datalines;
1000 England A G 0-14
1001 England A G 15-64
1002 England A H 15-64
1003 England B H 15-64
1004 England B J 15-64
1005 Norway A G 15-64
1006 Norway A H 65+
1007 Slovakia A G 65+
1008 Slovakia B H 65+
;
run;

This is a solution in proc sql. It's not really long or complicated. I don't think you could do it any shorter using data step.

proc sql;
    create table want as
        select distinct country, sum(Building_code = 'A') as A_buildings, sum(Flat_code= 'G') as G_flats, sum(age_category='15-64') as adults
            from have
            group by country
    ;
quit;