BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

7 REPLIES 7
derekg
Obsidian | Level 7

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;
ballardw
Super User

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.

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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?

derekg
Obsidian | Level 7

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;
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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

ballardw
Super User

@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.

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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