Cross tabulation

[dac_js]

data have;
input
ID Sex Year Region $ Group Condition;
datalines;
1 1 2015 North 1 1
2 0 2015 North 2 1
3 0 2015 South 3 1
4 1 2015 South 1 1
5 0 2015 South 2 1
6 1 2015 East 3 1
7 1 2016 East 1 1
8 1 2016 East 2 1
9 0 2016 North 3 1
10 0 2017 North 1 1
11 0 2017 South 2 0
12 1 2017 South 3 0
13 1 2018 South 1 0
14 1 2018 South 2 0
15 0 2018 South 3 0
16 1 2018 South 1 0
17 1 2018 North 2 0
18 1 2018 North 3 0
19 0 2018 West 1 0
20 0 2018 West 2 0
;
run;

I am trying to create a table like below:

		Year
		2015	2016	2017	2018	Total
Sex	1	3 (50%)	1 (33%)	2 (67%)	3 (37.5%)	9 (%)
	0	3 (50%)	2 (67%)	1 (33%)	5 (62.5%)	11 (%)
Reagion	North	2 (33%)	1 (33%)	1 (33%)	2 (25%)	6 (30%)
	South	3 (50%)	2 (67%)	2 (67%)	4  (50%)	11 (55%)
	East	1 (17%)	0 (0%)	0 (0%)	0 (0%)	1 (5%)
	West	0 (0%)	0 (0%)	1 (0%)	2 (25%)	3 (15%)
Group	1	2  (33%)	1 (33%)	1 (33%)	3 (37.5%)	7 (35%)
	2	2  (33%)	1 (33%)	1 (33%)	3 (37.5%)	7 (35%)
	3	2 (33%)	1 (33%)	1 (33%)	2 (25%)	6 (30%)
Condition	1	6 (100%)	3 (100%)	1 (33%)	0 (0%)	10 (50%)
	0	0 (0%)	0  (0%)	2 (67%)	8 (100%)	10 (50%)

I can get the numbers by using separate PROC FREQ procedures. Is there any way to get the entire table using a single procedure?

Thanks in advance!

[andreas_lds · Accepted solution]

Maybe proc tabulate, but i doubt that the layout will match your expectations fully.

options missing=0;

proc tabulate data=have;
   class Sex Year Region Group Condition;
   table
      Sex Region Group Condition
      ,
      Year*(n colpctn) All*(n colpctn)
   ;
run;

options missing=.;

[dac_js]

Thank you! This will work.

[Ksharp]

data have;
input
ID Sex Year Region $ Group Condition;
datalines;
1 1 2015 North 1 1
2 0 2015 North 2 1
3 0 2015 South 3 1
4 1 2015 South 1 1
5 0 2015 South 2 1
6 1 2015 East 3 1
7 1 2016 East 1 1
8 1 2016 East 2 1
9 0 2016 North 3 1
10 0 2017 North 1 1
11 0 2017 South 2 0
12 1 2017 South 3 0
13 1 2018 South 1 0
14 1 2018 South 2 0
15 0 2018 South 3 0
16 1 2018 South 1 0
17 1 2018 North 2 0
18 1 2018 North 3 0
19 0 2018 West 1 0
20 0 2018 West 2 0
;
run;

proc sql;
create table report as
select 1 as id,'Sex' as name length=80,
       put(sex,best.-l) as level length=80,
	   put(year,best.-l) as year length=80,
	   catx(' ',count(*),cats('(',
put(count(*)/(select count(*) from have where year=a.year),percentn8.2),')') ) as cell length=80
 from have as a
  group by sex,year
/*Sex Total*/
union all
select 1 as id,'Sex' as name length=80,
       put(sex,best.-l) as level length=80,
	   'Total',
	   catx(' ',count(*),cats('(',
put(count(*)/(select count(*) from have ),percentn8.2),')') ) as cell length=80
 from have as a
  group by sex
/**************************************/
union all
select 2 as id,'Region' as name length=80,
       Region as level length=80,
	   put(year,best.-l) as year length=80,
	   catx(' ',count(*),cats('(',
put(count(*)/(select count(*) from have where year=a.year),percentn8.2),')') ) as cell length=80
 from have as a
  group by Region,year
/*Region Total*/
union all
select 2 as id,'Region' as name length=80,
       Region as level length=80,
	   'Total',
	   catx(' ',count(*),cats('(',
put(count(*)/(select count(*) from have ),percentn8.2),')') ) as cell length=80
 from have as a
  group by Region

/**************************************/
union all
  select 3 as id,'Group' as name length=80,
       put(Group,best.-l) as level length=80,
	   put(year,best.-l) as year length=80,
	   catx(' ',count(*),cats('(',
put(count(*)/(select count(*) from have where year=a.year),percentn8.2),')') ) as cell length=80
 from have as a
  group by Group,year
/*Group Total*/
union all
select 3 as id,'Group' as name length=80,
       put(Group,best.-l) as level length=80,
	   'Total',
	   catx(' ',count(*),cats('(',
put(count(*)/(select count(*) from have ),percentn8.2),')') ) as cell length=80
 from have as a
  group by Group
/**************************************/
union all
  select 4 as id,'Condition' as name length=80,
       put(Condition,best.-l) as level length=80,
	   put(year,best.-l) as year length=80,
	   catx(' ',count(*),cats('(',
put(count(*)/(select count(*) from have where year=a.year),percentn8.2),')') ) as cell length=80
 from have as a
  group by Condition,year
/*Condition Total*/
union all
select 4 as id,'Condition' as name length=80,
       put(Condition,best.-l) as level length=80,
	   'Total',
	   catx(' ',count(*),cats('(',
put(count(*)/(select count(*) from have ),percentn8.2),')') ) as cell length=80
 from have as a
  group by Condition
;




/*Padding zero for missing level of year*/
create table report2 as
select a.*,coalescec(b.cell,'0(0.00%)') as cell length=80 from
(
select * from (select distinct id,name,level from report),(select distinct year from report)
) as a natural left join report as b 
;
quit;





proc report data=report2 nowd;
column name level year,cell ;
define name/group order=data ' ';
define level/group order=data ' ';
define year/across order=data;
define cell/group ' ';
run;

[dac_js]

Thank you for taking time to solve my problem! This creates exactly what I asked for.

[Patrick]

@dac_js 

What @Ksharp's solution demonstrates is that you can create "any" report using SAS but that there isn't always a ready made procedure to do exactly what you had in mind.

The design decision you have to make: Do you need exactly the report as initially designed or is it o.k. to present the information a bit differently using what the SAS proc's allow you to do "easily" and though having code that's simpler and easier to understand and maintain.

[dac_js]

Thank you for the suggestion!