topic Re: Creating a multiway table in SAS Programming

Creating a multiway table

Priyamvada07 — Tue, 13 Apr 2021 06:22:14 GMT

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!

Re: Creating a multiway table

andreas_lds — Tue, 13 Apr 2021 07:05:05 GMT

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;

Re: Creating a multiway table

Ksharp — Tue, 13 Apr 2021 12:18:41 GMT

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;

Re: Creating a multiway table

ChrisNZ — Thu, 15 Apr 2021 23:14:53 GMT

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.