Creating a multiway table

[Priyamvada07]

I have the following data

data have;
input year_2000 year_2001 year_2002 area gender;
cards;
1 1 1 6 1
1 1 1 8 1
1 1 0 8 2
0 1 1 6 1
1 1 1 8 1
;
run;

I am looking at creating the following table with sum value of '1' under year_2000 to year_2002, stratified by 'area' and 'gender', separately. In the end the 'total' row is total in each year (by 'area' or 'gender', it will be the same).

Any guidance on how to go about making this table?

Thanks!

[andreas_lds]

First transpose the data:

data transposed;
   set have;
   
   length group $ 10 subgroup 8 year $ 4 value 8;
   
   array years year_:;
   
   do i = 1 to dim(years);
      year = scan(vname(years[i]), 2, '_');
      value = years[i];
      
      group = 'gender';
      subgroup = gender;      
      output;
      
      group = 'area';
      subgroup = area;
      output;
   end;
   
   drop i year_: area gender;
run;

Afterwards proc report could be used, except for the total-row. i thought about using a multilabel format, but wasn't able to create the expected output:

proc report data=work.transposed;
   columns group subgroup year, value;
   
   define group / group ;
   define subgroup / group;
   define year / across;
   define value / sum;
run;

[Ksharp]

data have;
input year_2000 year_2001 year_2002 area gender;
cards;
1 1 1 6 1
1 1 1 8 1
1 1 0 8 2
0 1 1 6 1
1 1 1 8 1
;
run;

proc sql;
create table want as
select 'Area' as a length=40,put(area,best. -l) as b length=40,
       sum(year_2000) as year_2000 label='2000',
	   sum(year_2001) as year_2001 label='2001',
	   sum(year_2002) as year_2002 label='2002'
 from have
  group by area
union all
select 'Gender' as a length=40,put(gender,best. -l) as b length=40,
       sum(year_2000) as year_2000 label='2000',
	   sum(year_2001) as year_2001 label='2001',
	   sum(year_2002) as year_2002 label='2002'
 from have
  group by gender
union all
select 'Total','Total',
       sum(year_2000) as year_2000 label='2000',
	   sum(year_2001) as year_2001 label='2001',
	   sum(year_2002) as year_2002 label='2002'
 from have
 ;
 quit;

 proc report data=want nowd spanrows;
 column a b ('Year' year_2000 year_2001 year_2002);
 define a/group 'Characteristics' style={vjust=m};
 define b/display 'Sub Group';
run;

[ChrisNZ · Accepted solution]

If you want a similar result without additional processing.

proc tabulate data=HAVE;
  class  AREA GENDER;
  var YEAR_2000 YEAR_2001 YEAR_2002 ;
  table AREA GENDER all,(YEAR_2000 YEAR_2001 YEAR_2002)*sum=' ';
run;

This data storage layout is really bad though.