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 |
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.
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;
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.
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?
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;
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.);
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.