topic Re: How to display both formatted and unformatted variables in Proc Tabulate in SAS Programming

How to display both formatted and unformatted variables in Proc Tabulate

LL5 — Wed, 06 Dec 2017 20:30:04 GMT

Hi All, I have variable named color in the dataset test shown as below. I also have a Proc Format to assign a value to each different color. My end goal is to display the variables of both color and color_new in the proc tabulate shown as below. I think Proc Report might be able to achieve this goal, but is there anyway to do it in Proc Tabulate? Thanks for any of your advices.

DATA TEST;

LENGTH COLOR $6;

INPUT COLOR$ PRICE;

CARDS;

BLUE 75

ORANGE 200

BLACK 300

;

RUN;

PROC FORMAT;

VALUE $COLOR_NEW

"BLUE" ="HIGH"

"ORANGE" ="MID"

"BLACK" = "LOW"

;

RUN;

The below two Proc Tabulate could only display one variable

PROC TABULATE DATA=TEST;

VAR PRICE;

CLASS COLOR;

TABLE COLOR , PRICE;

RUN;

PROC TABULATE DATA=TEST;

FORMAT COLOR $COLOR_NEW.;

VAR PRICE;

CLASS COLOR;

TABLE COLOR , PRICE;

RUN;

The end goal that I want to achieve in Proc Tabulate is shown as below, is there anyway to do it?

		PRICE
		Sum
COLOR	COLOR_NEW
BLACK	LOW	300
BLUE	HIGH	75
ORANGE	MID	200

Re: How to display both formatted and unformatted variables in Proc Tabulate

derekg — Wed, 06 Dec 2017 20:53:07 GMT

How about something like this?

DATA NEWDATA;
	SET TEST;
	COLOR_NEW = COLOR;
RUN;


PROC TABULATE DATA=NEWDATA;
	FORMAT COLOR_NEW $COLOR_NEW.;
	VAR PRICE;
	CLASS COLOR COLOR_NEW;
	TABLE COLOR*COLOR_NEW , PRICE;
RUN;

Re: How to display both formatted and unformatted variables in Proc Tabulate

ballardw — Wed, 13 Dec 2017 17:59:06 GMT

To display an additional column in the row header in proc tabulate you would need to add a new variable:

PROC FORMAT;
   VALUE $COLOR_NEW
   "BLUE" ="HIGH"
   "ORANGE" ="MID"
   "BLACK" = "LOW"
   ;
RUN;
DATA TEST;
   LENGTH COLOR $6;
   INPUT COLOR$ PRICE;
   color_new = put(color,color_new.);
CARDS;
 
BLUE 75
ORANGE 200
BLACK 300
;
RUN;
 
PROC TABULATE DATA=TEST;
   VAR PRICE;
   CLASS COLOR color_new;
   TABLE COLOR*color_new , PRICE;
RUN;

Other approaches in Proc Tabulate are likely to run into issues with appearance except fixed width fonts if you want a nice alignment.

Proc report may be more appropriate if you only want one variable in your data set as you can create a column with calculated values referencing other variables.

Re: How to display both formatted and unformatted variables in Proc Tabulate

LL5 — Wed, 06 Dec 2017 21:51:12 GMT

Thanks, Ballardw. Now I realize a new variable must be added in order to be displayed in Proc Tabulate. Can you advise me how would I do it in Proc Report?

Re: How to display both formatted and unformatted variables in Proc Tabulate

derekg — Wed, 06 Dec 2017 22:17:51 GMT

You could use a compute block in Proc Report:

proc report data=test;
	columns color color2 price;
	define color / display;
	define color2 / computed;
	define price / display;
	compute color2 / character;
		color2 = put(color, color_new.);
	endcomp;
run;

Re: How to display both formatted and unformatted variables in Proc Tabulate

LL5 — Wed, 06 Dec 2017 22:42:07 GMT

Thanks Dereky for your codes. May I ask why we don't need the dollar sign in front of color_new?

color2 = put(color, color_new.);

Re: How to display both formatted and unformatted variables in Proc Tabulate

ballardw — Wed, 06 Dec 2017 23:23:56 GMT

@LL5 wrote:

Thanks Dereky for your codes. May I ask why we don't need the dollar sign in front of color_new?

color2 = put(color, color_new.);

typo. the format should include the $.

You could run the code and get the error message if you want to test if the code is valid as presented. We are human and will make mistakes especially when using names/ name style we don't normally use.

Re: How to display both formatted and unformatted variables in Proc Tabulate

LL5 — Wed, 06 Dec 2017 23:38:09 GMT

Hi thanks all! Actually it worked with or without the $, so that's why I'm curious about it.