topic Re: Proc Tabulate - Using a character variable in the VAR Statement in ODS and Base Reporting

Proc Tabulate - Using a character variable in the VAR Statement

sam2 — Thu, 01 Sep 2016 13:40:53 GMT

good morning, I am trying to create a cross tab table using proc tabulate, but am having a difficult time because one of my variables is a character. I am relatively new to using SAS and am unsure if there is a way to format the variable or transform the variable so it will fit in the body of my table. The data I am using looks somewhat like this:

data try;

input Market $ Number Type $;

datalines;

Eastern 17 Low

South 25 Mid

Midwest 92 High

West 33 Mid

;

run;

Market would be my class variable in proc tabulate and then I would like two columns, one with the number and one with the type. My tabulate statement looks like this:

proc tabulate data=try;

class Market;

var Number;

var Type;

table Market, Number='Count' Type;

run;

but when I run it, I get the inevitable error:

105

106 proc tabulate data=try;

107 class Market;

108 var Number;

109 var Type;

ERROR: Variable Type in list does not match type prescribed for this list.

110 table Market, Number='Count' Type;

111 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE TABULATE used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

is there a way to get around this problem?

thank you for any assistance you can give.

Re: Proc Tabulate - Using a character variable in the VAR Statement

howarder — Thu, 01 Sep 2016 13:52:55 GMT

You need 'Type' in the class statement as well since it is a character variable.

Re: Proc Tabulate - Using a character variable in the VAR Statement

howarder — Thu, 01 Sep 2016 13:56:08 GMT

Also I believe that this code will help you get the result that you are trying to get..

proc tabulate data=try;
class Market Type;
var Number;
table Market*type,Number='Count';
run;

Re: Proc Tabulate - Using a character variable in the VAR Statement

sam2 — Thu, 01 Sep 2016 14:12:38 GMT

thank you for the assistance Howarder,

When I run it the way you suggest I get a cross tab table that looks like this;

Market Type

Eastern Low 17

Midwest High 92

etc.

is there a way to get the Type character variable to be placed in the body of the cross tab?

Market Count Type

Eastern 17 Low

Midwest 92 High

etc.

thank you again for all your help.

take care,

sam

Re: Proc Tabulate - Using a character variable in the VAR Statement

Astounding — Thu, 01 Sep 2016 14:25:13 GMT

Here's a different structure that looks more like a cross-tab ... worth playing with if you like the idea:

proc tabulate data=try;

class Market Type;

var Number;

table Market, Type=' ' * Number=' ' * sum=' ';

run;

Re: Proc Tabulate - Using a character variable in the VAR Statement

sam2 — Thu, 01 Sep 2016 14:36:46 GMT

Thank you Astounding, I played with this a little and it still slightly misses what I am trying to get to. I need for my table to have the market area as the row header and then in the body of the report, I get a Column specifically with the Count number in it and another column with the description of the market in it or Type. Is it possible to place character variables in a column instead of a numeric variable? So the way I am looking for this to set up is:

Market Count Type

Eastern 17 Low

etc.

As always, I appreciate all the assistance.

sam

Re: Proc Tabulate - Using a character variable in the VAR Statement

Astounding — Thu, 01 Sep 2016 14:46:27 GMT

How is this any different than using PROC PRINT?

It is possible that PROC TABULATE is not the right tool for the job.

Re: Proc Tabulate - Using a character variable in the VAR Statement

sam2 — Thu, 01 Sep 2016 14:59:39 GMT

Oh it is very possible that proc tabulate isn't what I should be using. I am simplifying the data, so a lot of other things are going on within the proc tabulate piece, but the sticking point I am having is putting the character variable into the body of the chart. Ultimately, the chart has 14 columns, there are calculations occurring, several subcategories, I feel like it would best be done with proc tabulate or proc report, but with either of them I can't seem to add the Character variables to the body. Is there a way that you would know to get around this, or is there a better process to use given your limited knowledge of my data set.

Thank you,

sam

Re: Proc Tabulate - Using a character variable in the VAR Statement

Astounding — Thu, 01 Sep 2016 15:10:05 GMT

Based on everything you have mentioned, taken as a whole, I think the approach should be:

Perform all the calculations (without generating a report), and save the results in a data set.
Use PROC PRINT on the calculated data set.

If I knew more details I might think about it differently, but there is no easy way for PROC TABULATE to print a character field as a column in the middle of the report.

Re: Proc Tabulate - Using a character variable in the VAR Statement

howarder — Thu, 01 Sep 2016 15:23:32 GMT

If you want the columns in a certian order, you may want to look into Proc Report to create your desired output.

Re: Proc Tabulate - Using a character variable in the VAR Statement

ballardw — Thu, 01 Sep 2016 15:35:54 GMT

The only way to get text in the order you are indicating is to have the type value represented by a numeric value that can appear on the Var statement AND use a custom format with an appropriate statistic. If your "Type" is dependent on the Count variable this might be the best approach.

Please examine:

Proc format library=work;
invalue type
   'Low'  = 1
   'Mid'  = 2
   'High' = 3
;
value type
   1 = 'Low'
   2 = 'Mid'
   3 = 'High'
;
run;
data try;
   informat type type.;
   format   type type.;
input Market $ Number Type ;
datalines;
Eastern 17 Low
South 25 Mid
Midwest 92 High
West 33 Mid
;
run;
proc tabulate data=try;
   class Market;
   var Number;
   var Type;
table Market, Number='Count'*sum='' Type*max=''*f=type.;
run;

I read the data with a custom format, you could add a new variable such a TypeNum using

Typenum = input(Type,Type.);

and use that numeric variable with the format.

Caution: This may not work if crossing type with other statistics and if your Type changes within Market the result isn't reliable.

Re: Proc Tabulate - Using a character variable in the VAR Statement

sam2 — Thu, 01 Sep 2016 17:25:53 GMT

Thank you Ballardw, this is doing what I want it to do. I apologize, but I am not strong on what you have done. In my original dataset, I am reading in the data before I start proc tabulate, so the data is already read in, can you show me how I would do this if you read in this file first?

thanks for all the assistance,

sam

Re: Proc Tabulate - Using a character variable in the VAR Statement

ballardw — Thu, 01 Sep 2016 17:50:25 GMT

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

I usually turn Excel files into CSV and import according to rules about what data types and expected values in the data set.

If you already have a SAS data set then this link: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show you how to create datastep text that can be posted here, either paste into the window or attach as a TXT file that we can then run and recreate your data. You only need to include enough data to recreate significant issues. Generally at least two values of any category and if missing values need special treatment then some of those.

Re: Proc Tabulate - Using a character variable in the VAR Statement

sam2 — Thu, 01 Sep 2016 18:20:34 GMT

I apologize Ballardw, that is a mistake I will not make again. I was giving you a file that you could read in, the data is the same as what the datastep did before, but I think you will see what I am trying to do from this. If the data is already read, can you format a particular column from that data that is a character into a numeric and allow me to put it in a proc tabulate column.

so, instead of formatting and then reading in the data, do it something like this:

data try;

input Market $ Number Type $;

datalines;

Eastern 17 Low

South 25 Mid

Midwest 92 High

West 33 Mid

;

run;

Proc format library=work;

invalue type

'Low' = 1

'Mid' = 2

'High' = 3

;

value type

1 = 'Low'

2 = 'Mid'

3 = 'High'

;

run;

data try2;

set try;

informat type type.;

format type type.;

run;

proc tabulate data=try;

class Market;

var Number;

var Type;

table Market='', Number='Count'*sum='' Type*max=''*f=type.;

run;

this does not run for me when I try it like this, I know it is in what I am doing, but can you provide assistance?

thank you again and have a great day!

sam

Re: Proc Tabulate - Using a character variable in the VAR Statement

ballardw — Thu, 01 Sep 2016 22:42:19 GMT

Please post code using the ^^ Run icon above.

You had several Issues: First in your TRY2 dataset you did not create a numeric value. You generated warnings because you attempted to use numeric format and informat with a variable Try that was character. Here is how to create a numeric variable from

your existing type.

Second your Proc Tabulate should reference the new dataset, Try2. You were still pointing it to the original Try. Then you need to use the numeric version of the variable. Make sure to reference it in the VAR and Table statements with the same name.

data try2;
   set try;
   format typeNum type.;
   TypeNum = input(type,type.);
run;
/* two things: First you would need your Try2 set
and use the numeric version*/
proc tabulate data=try2;
class Market;
var Number;

var TypeNum;
table Market='', Number='Count'*sum='' TypeNum*max=''*f=type.;
run;

Re: Proc Tabulate - Using a character variable in the VAR Statement

Cynthia_sas — Fri, 02 Sep 2016 00:51:51 GMT

Hi: Take a look at the example in this paper
http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf
at the example on pages 5 and 6 of using PROC REPORT to get a character variable in a cell in a table.

cynthia

Re: Proc Tabulate - Using a character variable in the VAR Statement

sam2 — Tue, 06 Sep 2016 14:43:12 GMT

thank you to everyone who helped solve this problem, I appreciate it more than you will know.