Search code examples
excelsascrosstabods

How do I vertically stack tables in SAS using ODS Excel?

I'm exporting a series of crosstabs from SAS 9.3 to Excel using Excel ODS tagsets.

The syntax I've written is as follows:

%include 'C:\Users\MyName\Documents\excltags.tpl';
ods tagsets.excelxp file = "M:\Assess\1718\PARTIC_3-8.xml"

proc sort data = assess_elem;
   by accommodations_ny participation_ny district;
run;

proc freq data = assess_elem
   by accommodations_ny participation_ny district;
   tables grade*proficiency / crosslist nocum nocol;
run;

ods tagsets.excelxp close;

The variables are:

  • accommodations_ny: alphanumeric, N/Y
  • participation_ny: alphanumeric, N/Y
  • district: alphanumeric, 103 district names
  • grade: numeric, 3-8
  • proficiency: numeric, 0/1

The syntax above creates a table like this for each of the 103 districts:

have 103 separate tables

What I would like to have is something more like:

want 1 stacked table

Solution

  • You are not limited to two way in the TABLE statement, especially if you use the LIST option instead of CROSSLIST.

    Here's a quick example using SASHELP.CARS

    proc freq data=sashelp.cars;
table origin*make*cylinders / list;
run;

    It creates a table like this:

    enter image description here

    This will not give you the unrepeated columns on the left most, if you do want that, PROC TABULATE gives you more control over the output or push it to a table and use PROC REPORT.