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).

Priyamvada07_0-1618294625861.png

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;

x.jpg

[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;

ChrisNZ_0-1618528417980.png

This data storage layout is really bad though.