1

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:

enter image description here

Solution:

enter image description here

Thank you for your time.

Kristián Óvári
  • 33
  • 4
  • 1
    Next time put your data into text, not into picture. It is really a pain to retype everything... – Negdo Nov 03 '22 at 06:32

1 Answers1

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;
Negdo
  • 507
  • 2
  • 8