DATA Step, Macro, Functions and more

How to display both formatted and unformatted variables in Proc Tabulate

Accepted Solution Solved
Reply
Contributor LL5
Contributor
Posts: 55
Accepted Solution

How to display both formatted and unformatted variables in Proc Tabulate

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


Accepted Solutions
Solution
‎12-12-2017 09:47 PM
Super User
Posts: 13,583

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

[ Edited ]

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.

View solution in original post


All Replies
Occasional Contributor
Posts: 9

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

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;
Solution
‎12-12-2017 09:47 PM
Super User
Posts: 13,583

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

[ Edited ]

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.

Contributor LL5
Contributor
Posts: 55

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

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?

Occasional Contributor
Posts: 9

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

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;
Contributor LL5
Contributor
Posts: 55

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

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.);

Super User
Posts: 13,583

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


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.

Contributor LL5
Contributor
Posts: 55

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

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 167 views
  • 5 likes
  • 3 in conversation