topic Re: Proc Tabulate - how to remove, or select, part of a var in SAS Procedures

Proc Tabulate - how to remove, or select, part of a var

J_Walker — Thu, 15 Sep 2011 13:11:27 GMT

I am trying to export multiple tables to excel using proc tabulate. For each table I want to use only part of a var. For example, for states*population I want to only look at eastern states in one table. In the next central etc. I tried using a few where statements but SAS only used the last one in the proc tabulate. Any ideas?

Thanks

Proc Tabulate - how to remove, or select, part of a var

TM — Thu, 15 Sep 2011 13:17:13 GMT

I believe that you can use "drop" and "keep" in your proc tabulate statement..

Check: http://www2.sas.com/proceedings/sugi27/p060-27.pdf

Proc Tabulate - how to remove, or select, part of a var

J_Walker — Thu, 15 Sep 2011 13:24:13 GMT

But that would drop a whole var. I would like use the var but only for select values.

Proc Tabulate - how to remove, or select, part of a var

Cynthia_sas — Thu, 15 Sep 2011 13:59:12 GMT

Hi:

You do not say how you are doing your export. Are you creating an output data set with PROC TABULATE and using PROC EXPORT or the LIBNAME engine to Excel for the export? Or, are you creating an HTML or XML or CSV file using ODS HTML or ODS TAGSETS.EXCELXP or ODS CSV???

Aside from how you're getting your results into Excel, the WHERE statement might work to allow you to produce one table for Eastern states versus one table for Central states. Another approach would be to create a "region" variable in your data (possibly with a user-defined format) and run your PROC TABULATE with a BY REGION; statement. A third approach would be to use a REGION variable with the PAGE dimension in your table statement -- with a PAGE variable, PROC TABULATE would create a separate table for each unique value of the PAGE dimension variable value. Yet another approach would be to write a SAS Macro program; however, I believe you have enough other possibilities without going down the macro road.

cynthia

Proc Tabulate - how to remove, or select, part of a var

J_Walker — Thu, 15 Sep 2011 14:41:48 GMT

Thanks Cynthia,

The code to export is:

ods tagetsets.EXCELXP

ods noresults;

proc tabulate data=

I'd like to use the where statement but I am creating multiple table and need multiple where statements. Unless there is a way to prevent SAS from overwriting the previous where statement that won't work. I get the NOTE: WHERE clause has been replaced.

I could create a new data set but I dealing with too many var and tables to make that practical. I guess I was hoping that there was someway to prevent the WHERE clause from being replaced.

Re: Proc Tabulate - how to remove, or select, part of a var

Cynthia_sas — Thu, 15 Sep 2011 15:25:12 GMT

Hi,

When you use ODS TAGSETS.EXCELXP, you are not doing a true "export" (from SAS dataset to binary XLS proprietary format). Instead, you are creating a Spreadsheet Markup Language 2003 XML file that Excel knows how to open and render because it is in an ASCII text file format that conforms to this Office 2003 XML specification.

But, I don't understand what kind of code you're submitting to get the WHERE CLAUSE REPLACED message. It would be inappropriate to have 2 where clauses in one TABULATE step. In the code below, I show 4 different examples of creating multiple tables for Excel using ODS TAGSETS.EXCELXP. The first 3 examples only use variables that exist in SASHELP.CLASS. The 4th example creates a CATEGORY variable based on age with a user-defined format and then runs 1 TABULATE step to create multiple tables.

cynthia

options nodate nonumber;
title;
ods listing close;

** Example 1: 2 TABULATES with 2 WHERE clauses;
ods tagsets.excelxp file='c:\temp\examp1.xml' style=sasweb;
proc tabulate data=sashelp.class f=comma6.;
title "Male Students";
where sex = 'M';
class age;
var height weight;
table age=' ',
        height*(n min mean max) weight*(n min mean max)
/ box=Age;
run;

proc tabulate data=sashelp.class f=comma6.;
title "Female Students";
where sex = 'F';
class age;
var height weight;
table age=' ',
        height*(n min mean max) weight*(n min mean max)
/ box=Age;
run;
ods _all_ close;

** Example 2: 1 TABULATE with BY group processing;
proc sort data=sashelp.class out=class;
by sex;
run;

ods tagsets.excelxp file='c:\temp\examp2.xml' style=sasweb;
proc tabulate data=class f=comma6.;
by sex;
title 'Student Statistics with BY';
class age;
var height weight;
table age=' ',
        height*(n min mean max) weight*(n min mean max)
/ box=Age;
run;
ods _all_ close;

** Example 3: 1 TABULATE with PAGE dimension;
ods tagsets.excelxp file='c:\temp\examp3.xml' style=sasweb;
proc tabulate data=class f=comma6.;
title 'Student Statistics with PAGE';
class sex age;
var height weight;
table sex,   /* Page */
        age,   /* Row */
        height*(n min mean max) weight*(n min mean max)
/ box=_page_;
run;
ods _all_ close;


** Example 4: Making a "category" variable based on age;
**            and then using that variable with BY processing;
proc format;
value agef 11-14 = 'Cannot Take Driving Lessons'
             15-16 = 'Potential Student Drivers';
run;

data cat_class;
set sashelp.class;
category = put(age,agef.);
run;

proc sort data=cat_class;
by category age;
run;

ods tagsets.excelxp file='c:\temp\examp4.xml' style=sasweb;
proc tabulate data=cat_class f=comma6.;
by category;
title 'Statistics by Driving Category';
class category age;
var height weight;
table age=' ',
        height*(n mean) weight*(n mean)
/ box=Age;
run;
ods _all_ close;

Proc Tabulate - how to remove, or select, part of a var

J_Walker — Thu, 15 Sep 2011 18:34:17 GMT

Thank You again.

I guess I should back track. I am creating a chapter in a book. Each chapter takes one factor and splits it by a number of different factors. The data for each chapter was created using a BY statement. For one chapter it took 50 different BY statements for 13 different variables. The BY statement creates a firstdim and a seconddim variable. This created a problem because the data table it created was huge and unreadable. So a new variable was created with lables for each category of the variable but created a 'MISSING" label for the rest.

So we started with something like this from the BY statements (X = firstdim Y = 2ndDim)

X Y Z

a 6 g

b 7 h

c 8 i

d 9 j

e 7 k

f 1 l

And ended up with something like this were X was broken to create two new variables with lables and the same for Z

X Y Z W A B C

a 6 g a ' ' g ' '

b 7 h b ' ' h ' '

c 8 i c ' ' i ' '

d 9 j ' ' d ' ' j

e 7 k ' ' e ' ' k

f 1 l ' ' f ' ' f

Now using the PROC TABULATE statement with MISSING to create tables

Var Y

class W A B C;

table W*B, Y;

table W*C, Y;

table A*B, Y;

table A*C, Y;

The problem is that this creates a value for the MISSING variable (which is the sum of the Y for the missing) when I am not interested in it. If I use a where startment to select only A's with values than I end up with W's with no values. Because I am creating so many tables and I would like each chapter to be in one spreadsheet. I could modify the data but it would be easier to create a MACRO in excel....which I guess is where I am at. Thank you for your help.