Delete columns in Proc Tabulate output

[ebowen]

I'm using Proc Tabulate to break out the wages and percentages of people in my state vs. people out of state based on their education level. I've managed to output what I want, but I really don't need the data from people out of state, so I would like to eliminate that from the output. Essentially, I'm trying to delete a column from the output based on the value of the data in the class variable. Here's my tabulate procedure as it stands:

PROC TABULATE DATA=employment;

class Year InState Degree;

var Wage;

table Year='', ALL InState*Degree*(pctn<inState> Wage*mean) /nocellmerge;

RUN;

Is there any way to get rid of the output for just the out of state people based on the value of the InState variable? I've tried using a WHERE clause to limit the data to only in-state people, but that messes up the percentage variable. I am outputting the table via ODS, so if there's a way to eliminate the column from ODS that would work too.

Thanks for your help!

[Astounding]

If all else fails, here's the general solution to this type of problem.

1. Create a summary data set from PROC TABULATE, instead of printing.

2. Subset the summary data set to contain just the values that should go into the report.

3. Use the modified summary data set as the input to a second PROC TABULATE that actually prints the report.

The final PROC TABULATE will be a little different than the original.  Since it is only printing precalculated numbers, it will use the SUM statistic all the time.

Good luck.

[Cynthia_sas]

Hi:

  When you say you are outputting the table via ODS, are you using ODS HTML, ODS RTF or ODS PDF?

  TABULATE does not have a way to delete report columns (or rows). Normally, if you are calculating information with TABULATE and you don't want to show certain rows or columns, then you create an output dataset from TABULATE and delete the information you do NOT want, after the percentages are  calculated. Then you pass this filtered data to another procedure (such as PROC PRINT or PROC REPORT).

  The thing is that the output dataset from TABULATE does not maintain the same structure as what you see in the output report. So, you may have to do some data manipulation to "massage" the data that you get out from TABULATE into the form that you want.

  For example, I mocked up some sample data -- just using Y or N for the INSTATE variable. If you look at the output from TABULATE (WORK.PCTOUT) versus ODS, you will see how the structure changes.

cynthia

data employment;
  length instate $1 degree $3;
  infile datalines dlm=' ';
  input year instate $ degree $ wage;
return;
datalines;
2009       y        MS       24411
2009       y        PhD      29282
2009       n        MS       27923
2010       y        PhD      29974
2010       n        MS       24845
2010       n        MS       27576
2010       n        PhD      39697
2011       y        MS       24718
2011       y        MS       27709
2011       y        PhD      29380
2011       n        MS       24951
2012       y        MS       25202
2012       y        MS       27373
2012       y        PhD      39764
2009       n        MS       25415
2009       n        PhD      29286
2009       n        MS       27927
2010       n        PhD      29978
2011       n        MS       25719
2011       n        MS       27700
2011       n        PhD      29381
2011       n        MS       25952
2012       n        MS       25203
2012       n        PhD      39764
2009       y        PhD      39285
2009       y        MS       37926
2010       y        PhD      39977
2010       y        MS       33848
2010       y        MS       35579
2010       y        PhD      45690
2011       y        MS       37711
2011       y        PhD      39382
2011       y        MS       35953
2012       n        MS       37374
2012       y        PhD      49765
;
run;
  
ods listing close;
ods html file='c:\temp\emp_report.html';
PROC TABULATE DATA=employment out=pctout;
class Year InState Degree;
var Wage;
table Year='',
      ALL InState*Degree*(n pctn<inState> Wage*mean)
      /nocellmerge;
RUN;
ods html close;

[ebowen]

Thanks for such useful answers, both of you. I'm actually using ODS tagsets.excelxp to output the data. I have noticed that ODS html and pdf have a few extra ways to hide columns, etc., so I might consider doing that. Outputting a dataset and filtering the data might work for me too, so I'll look into doing that.

[sam_sas2]

Hi! Could you guide on how to use PROC HTML to hide my columns? Thanks in advance! 🙂

[Cynthia_sas]

Hi:
HTML is not a "PROC". HTML is a destination. You use ODS HTML To create output from SAS procedures like PROC REPORT and PROC TABULATE. ODS HTML does not really provide a way to hide columns. ODS EXCEL and ODS TAGSETS.EXCELXP do provide ways to hide columns, but it's not always easy to figure the columns that you want to hide and I've never tried to use columns that might have spanning headers above them.

The usual way to hide columns, no matter what procedure you're using in the scenario where you want to use ALL the values for the overall count, but you want to not show some columns or subgroups for some reason is to make 2 passes through the data. One pass to summarize and get the numbers and statistics using ALL the values. Then you create an output dataset from the first pass and use the summarized dataset to pass to your report procedure. Usually in between the first pass and the final report pass, you eliminate the rows or columns you don't want with a DATA step program or some other kind of subsetting procedure.

Cynthia