Proc Tabulate: Adding lookup values to ALL totals

[Jeff_DOC]

Good morning. I am having an issue with proc tabulate (at least I think that's my best option on how to proceed). The tabulate code I have works perfectly except I need to add two columns after the row total column. Both new columns need to be either a lookup value or a value from proc format and both are based on the total points per row. You can see from below that I have the proc tabulate written and it works great. I've also written the proc format just in case that's my best option. I'm just not sure how to put the proc format into play, or even if I can? The "HAVE" is missing two columns at the end. The "WANT" shows the two columns with the correct values.

Thank you very much. I'd sure appreciate any help or direction.

PS: The proc tabulate comments are for my benefit, they're not intended to explain this group how to use proc tabulate: I'm sure no one but me needs that. 🙂

proc format;
value earned_level
0 - 15 = 'Not Enough Points'
16 - 75 = '$8.00'
76 - 79 = '$24.00'
80 - 85 = '$26.00'
86 - 106 = '$30.00'
107 - 127 = '$34.00'
128 - 148 = '$38.00'
149 - 169 = '$42.00'
170 - 190 = '$46.00'
191 - 211 = '$50.00'
212 - 232 = '$54.00'
233 - 253 = '$58.00'
254 - 274 = '$62.00'
275 - 295 = '$66.00'
296 - 316 = '$70.00'
317 - 337 = '$74.00'
338 - 358 = '$78.00'
359 - high= '$82.00';
value less_value
0 - 15 = 'Not Enough Points'
16 - 75 = '$87.60'
76 - 79 = '$22.80'
80 - 85 = '$24.70'
86 - 106 = '$28.50'
107 - 127 = '$32.50'
128 - 148 = '$36.10'
149 - 169 = '$39.90'
170 - 190 = '$43.70'
191 - 211 = '$47.50'
212 - 232 = '$51.30'
233 - 253 = '$55.10'
254 - 274 = '$58.90'
275 - 295 = '$62.70'
296 - 316 = '$66.50'
317 - 337 = '$70.30'
338 - 358 = '$74.10'
359 - high= '$77.90';
run;

ods listing close;
options missing = ' ' orientation= 'landscape' papersize='letter' topmargin= 1in bottommargin= 0.5in rightmargin= 0.5in leftmargin= 0.5in;
ods Excel file= "\\<<file_Path>>\pay_project_&SYSDATE..xlsx"
options(sheet_interval = 'none'
sheet_name = 'Summary'
frozen_headers = '2'
center_horizontal = 'yes'
suppress_bylines = 'yes'
absolute_column_width = '10, 30, 15, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12,
12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12');

proc tabulate data = met_10 style = [background=beige font_size=2.0] order = data;
/* Class must agree with table although table can contain things class does not.*/
/* Classlev lets you set attributes for class variables since the class statement will not allow styles.*/
/* Multiple classlev statements are allowed. This way you can set different variables to different styles.*/

class id_number name work_date PositionPoints / style=[font_size=2.0];
classlev work_date / style=[background=DMSWHITE /*DMSGRAY*/ font_size=2.0 cellwidth = 1000%];
classlev PositionPoints / style=[background=DMSWHITE /*DMSGRAY*/ font_size=2.0 textalign=center cellwidth = 1000%];

var points_paid;

/*All sets totals for the columns. Misstext gives a blank for missing data instead of a zero.*/
table id_number = 'SID '*name*PositionPoints ALL={label='Total' /*s=[just=center]*/},
(work_date = 'Date' ALL='Total Points') * (points_paid = ' ') * f=4.0 / misstext = ' ' style=[font_size=2] box = {label = ' '};

/*This removes the redundant statistic identifier in the table.*/
/*Ex. this removes the word "Sum" from the table headers.*/
keylabel sum= ' ' N= ' ';
title1 " TITLE 1";
title2 "Summary: Points Earned";
footnote1 "Source: Source File &sysdate.";
run;

ods Excel close;
ods listing;

HAVE:

			Date																						Total Points
			01NOV2021	02NOV2021	03NOV2021	04NOV2021	05NOV2021	08NOV2021	09NOV2021	10NOV2021	11NOV2021	12NOV2021	15NOV2021	16NOV2021	17NOV2021	18NOV2021	19NOV2021	22NOV2021	23NOV2021	24NOV2021	25NOV2021	26NOV2021	29NOV2021	30NOV2021
SID	Name	Position Points	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
100	NAME 1	18
200	NAME 2	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14									196
		16															16	16	16	16	16	16	16	16	128
300	NAME 3	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
400	NAME 4	14	14																						14
		16		16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	336
500	NAME 5	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
600	NAME 6	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
700	NAME 7	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
800	NAME 8	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	352
900	NAME 9	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
950	NAME 10	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
Total			936	952	934	934	934	934	934	934	912	912	948	948	948	948	966	966	966	966	950	950	950	990	21000

WANT:

			Date																						Total Points
			01NOV2021	02NOV2021	03NOV2021	04NOV2021	05NOV2021	08NOV2021	09NOV2021	10NOV2021	11NOV2021	12NOV2021	15NOV2021	16NOV2021	17NOV2021	18NOV2021	19NOV2021	22NOV2021	23NOV2021	24NOV2021	25NOV2021	26NOV2021	29NOV2021	30NOV2021		EARNED_LEVEL	LESS VALUE
SID	Name	Position Points	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
100	NAME 1	18																								$82.00	$77.90
200	NAME 2	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14									196	$50.00	$47.50
		16															16	16	16	16	16	16	16	16	128	$38.00	$36.10
300	NAME 3	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
400	NAME 4	14	14																						14	Not Enough Points	Not Enough Points
		16		16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	336	$74.00	$70.30
500	NAME 5	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
600	NAME 6	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
700	NAME 7	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
800	NAME 8	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	352	$78.00	$74.10
900	NAME 9	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
950	NAME 10	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
Total			936	952	934	934	934	934	934	934	912	912	948	948	948	948	966	966	966	966	950	950	950	990	21000

[Reeza]

No data to test anything but basically add your ALL in three times and apply a different format to it each time. 

*Create fake data;
data energy;
   length State $2;
   input Region Division state $ Type Expenditures;
   datalines;
1 1 ME 1 708
1 1 ME 2 379
1 1 NH 1 597
1 1 NH 2 301
1 1 VT 1 353
1 1 VT 2 188
1 1 MA 1 3264
1 1 MA 2 2498
1 1 RI 1 531
1 1 RI 2 358
1 1 CT 1 2024
1 1 CT 2 1405
1 2 NY 1 8786
1 2 NY 2 7825
1 2 NJ 1 4115
1 2 NJ 2 3558
1 2 PA 1 6478
1 2 PA 2 3695
4 3 MT 1 322
4 3 MT 2 232
4 3 ID 1 392
4 3 ID 2 298
4 3 WY 1 194
4 3 WY 2 184
4 3 CO 1 1215
4 3 CO 2 1173
4 3 NM 1 545
4 3 NM 2 578
4 3 AZ 1 1694
4 3 AZ 2 1448
4 3 UT 1 621
4 3 UT 2 438
4 3 NV 1 493
4 3 NV 2 378
4 4 WA 1 1680
4 4 WA 2 1122
4 4 OR 1 1014
4 4 OR 2 756
4 4 CA 1 10643
4 4 CA 2 10114
4 4 AK 1 349
4 4 AK 2 329
4 4 HI 1 273
4 4 HI 2 298
;

*create formats;

proc format;
   value regfmt 1='Northeast'
                2='South'
                3='Midwest'
                4='West';
   value divfmt 1='New England'
                2='Middle Atlantic'
                3='Mountain'
                4='Pacific';
   value usetype 1='Residential Customers'
                 2='Business Customers';
   value exp_cat low - 15000 = 'Introduction'
         15000 -30000 = "Middle"
         30000 - high = 'Large';
run;
proc tabulate data=energy format=dollar12.;
   class region division type;
   
   var expenditures;
   table region*division,
         (type all)*expenditures all*expenditures*f=exp_cat.
          / rts=25;
   format region regfmt. division divfmt. type usetype.;
   title 'Energy Expenditures for Each Region';
   title2 '(millions of dollars)';
run;

---

@Jeff_DOC wrote:

Good morning. I am having an issue with proc tabulate (at least I think that's my best option on how to proceed). The tabulate code I have works perfectly except I need to add two columns after the row total column. Both new columns need to be either a lookup value or a value from proc format and both are based on the total points per row. You can see from below that I have the proc tabulate written and it works great. I've also written the proc format just in case that's my best option. I'm just not sure how to put the proc format into play, or even if I can? The "HAVE" is missing two columns at the end. The "WANT" shows the two columns with the correct values.

Thank you very much. I'd sure appreciate any help or direction.

PS: The proc tabulate comments are for my benefit, they're not intended to explain this group how to use proc tabulate: I'm sure no one but me needs that. 🙂

proc format;
value earned_level
0 - 15 = 'Not Enough Points'
16 - 75 = '$8.00'
76 - 79 = '$24.00'
80 - 85 = '$26.00'
86 - 106 = '$30.00'
107 - 127 = '$34.00'
128 - 148 = '$38.00'
149 - 169 = '$42.00'
170 - 190 = '$46.00'
191 - 211 = '$50.00'
212 - 232 = '$54.00'
233 - 253 = '$58.00'
254 - 274 = '$62.00'
275 - 295 = '$66.00'
296 - 316 = '$70.00'
317 - 337 = '$74.00'
338 - 358 = '$78.00'
359 - high= '$82.00';
value less_value
0 - 15 = 'Not Enough Points'
16 - 75 = '$87.60'
76 - 79 = '$22.80'
80 - 85 = '$24.70'
86 - 106 = '$28.50'
107 - 127 = '$32.50'
128 - 148 = '$36.10'
149 - 169 = '$39.90'
170 - 190 = '$43.70'
191 - 211 = '$47.50'
212 - 232 = '$51.30'
233 - 253 = '$55.10'
254 - 274 = '$58.90'
275 - 295 = '$62.70'
296 - 316 = '$66.50'
317 - 337 = '$70.30'
338 - 358 = '$74.10'
359 - high= '$77.90';
run;

ods listing close;
options missing = ' ' orientation= 'landscape' papersize='letter' topmargin= 1in bottommargin= 0.5in rightmargin= 0.5in leftmargin= 0.5in;
ods Excel file= "\\<<file_Path>>\pay_project_&SYSDATE..xlsx"
options(sheet_interval = 'none'
sheet_name = 'Summary'
frozen_headers = '2'
center_horizontal = 'yes'
suppress_bylines = 'yes'
absolute_column_width = '10, 30, 15, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12,
12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12');

proc tabulate data = met_10 style = [background=beige font_size=2.0] order = data;
/* Class must agree with table although table can contain things class does not.*/
/* Classlev lets you set attributes for class variables since the class statement will not allow styles.*/
/* Multiple classlev statements are allowed. This way you can set different variables to different styles.*/

class id_number name work_date PositionPoints / style=[font_size=2.0];
classlev work_date / style=[background=DMSWHITE /*DMSGRAY*/ font_size=2.0 cellwidth = 1000%];
classlev PositionPoints / style=[background=DMSWHITE /*DMSGRAY*/ font_size=2.0 textalign=center cellwidth = 1000%];

var points_paid;

/*All sets totals for the columns. Misstext gives a blank for missing data instead of a zero.*/
table id_number = 'SID '*name*PositionPoints ALL={label='Total' /*s=[just=center]*/},
(work_date = 'Date' ALL='Total Points') * (points_paid = ' ') * f=4.0 / misstext = ' ' style=[font_size=2] box = {label = ' '};

/*This removes the redundant statistic identifier in the table.*/
/*Ex. this removes the word "Sum" from the table headers.*/
keylabel sum= ' ' N= ' ';
title1 " TITLE 1";
title2 "Summary: Points Earned";
footnote1 "Source: Source File &sysdate.";
run;

ods Excel close;
ods listing;

HAVE:

			Date																						Total Points
			01NOV2021	02NOV2021	03NOV2021	04NOV2021	05NOV2021	08NOV2021	09NOV2021	10NOV2021	11NOV2021	12NOV2021	15NOV2021	16NOV2021	17NOV2021	18NOV2021	19NOV2021	22NOV2021	23NOV2021	24NOV2021	25NOV2021	26NOV2021	29NOV2021	30NOV2021
SID	Name	Position Points	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
100	NAME 1	18
200	NAME 2	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14									196
		16															16	16	16	16	16	16	16	16	128
300	NAME 3	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
400	NAME 4	14	14																						14
		16		16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	336
500	NAME 5	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
600	NAME 6	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
700	NAME 7	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
800	NAME 8	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	352
900	NAME 9	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
950	NAME 10	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
Total			936	952	934	934	934	934	934	934	912	912	948	948	948	948	966	966	966	966	950	950	950	990	21000

WANT:

			Date																						Total Points
			01NOV2021	02NOV2021	03NOV2021	04NOV2021	05NOV2021	08NOV2021	09NOV2021	10NOV2021	11NOV2021	12NOV2021	15NOV2021	16NOV2021	17NOV2021	18NOV2021	19NOV2021	22NOV2021	23NOV2021	24NOV2021	25NOV2021	26NOV2021	29NOV2021	30NOV2021		EARNED_LEVEL	LESS VALUE
SID	Name	Position Points	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
100	NAME 1	18																								$82.00	$77.90
200	NAME 2	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14									196	$50.00	$47.50
		16															16	16	16	16	16	16	16	16	128	$38.00	$36.10
300	NAME 3	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
400	NAME 4	14	14																						14	Not Enough Points	Not Enough Points
		16		16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	336	$74.00	$70.30
500	NAME 5	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
600	NAME 6	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
700	NAME 7	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
800	NAME 8	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	352	$78.00	$74.10
900	NAME 9	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
950	NAME 10	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396	$82.00	$77.90
Total			936	952	934	934	934	934	934	934	912	912	948	948	948	948	966	966	966	966	950	950	950	990	21000

---

[Jeff_DOC]

Thank you very much for taking time to help. That works (sort of). It allows me to format the ALL column with the proc format. However, it replaces the total points column with the proc format values. I'd sure like to have them both (and the "less_value" actually from the proc format example). Sorry I did not include test data. I'll try to do that here in a text file.

Thank you. Here is the current proc tabulate example that is pretty close to what I need.

ods listing close;
options missing = ' ' orientation= 'landscape' papersize='letter' topmargin= 1in bottommargin= 0.5in rightmargin= 0.5in leftmargin= 0.5in;
ods Excel file= "<<PATH?>>\pay_project_&SYSDATE..xlsx"
options(sheet_interval = 'none'
sheet_name = 'Summary'
frozen_headers = '2'
orientation = 'portrait'
center_horizontal = 'yes'
suppress_bylines = 'yes'
absolute_column_width = '10, 35, 15, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12,
12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12');

proc tabulate data = met_10 style = [background=beige font_size=2.0] order = data;
/* Class must agree with table although table can contain things class does not.*/
/* Classlev lets you set attributes for class variables since the class statement will not allow styles.*/
/* Multiple classlev statements are allowed. This way you can set different variables to different styles.*/

class id_number name work_date /*PositionPoints*/ / style=[font_size=2.0];
classlev work_date / style=[background=DMSWHITE /*DMSGRAY*/ font_size=2.0 cellwidth = 1000%];
var pointspaid;

/*All sets totals for the columns. Misstext gives a blank for missing data instead of a zero.*/
table id_number = 'SID '*name ALL={label='Total' /*s=[just=center]*/},
(work_date = 'Date' ALL='Total Points') * (pointspaid = ' ' * f=4.0) / misstext = ' ' style=[font_size=2] box = {label = ' '};

/*This removes the redundant statistic identifier in the table.*/
/*Ex. this removes the word "Sum" from the table headers.*/
keylabel sum= ' ' N= ' ';
title1 "TITLE 1";
title2 "Summary: Points Earned";
footnote1 "Source: &sysdate.";
run;

ods Excel close;
ods listing;

[Reeza]

Take a look at the example code again and notice that I have ALL in there twice with one having an applied format and one having the old format.

[Reeza]

And it really helps if your code is legible - ie use a code block, indents and basic formatting.

[Jeff_DOC]

When I paste in my code the formatting and indenting is removed.

[Reeza]

---

@Jeff_DOC wrote:

When I paste in my code the formatting and indenting is removed.

---

You're not using an insert code block section. Use the 8th (7th if you have the log or want to highlight text) icon in the Editor to insert code.

[Jeff_DOC]

Hmmm. I have Run, Cancel, Save, Save As, Cut, Copy, Paste, Undo, Redo...That would make Paste my 7th icon and the 8th would be Undo? I tried using the Copy button but it removes everything just like a straight Paste.

[Reeza]

When you're pasting your code into the forum, paste it into a code block. 

Notice how the code I've posted is formatted and has a grey background. Yours is pasted straight into the HTML editor pane. 

---

@Jeff_DOC wrote:
Hmmm. I have Run, Cancel, Save, Save As, Cut, Copy, Paste, Undo, Redo...That would make Paste my 7th icon and the 8th would be Undo? I tried using the Copy button but it removes everything just like a straight Paste.

---

[Jeff_DOC]

Hi Reeza.

Thank you so much for your time and efforts. I found a different approach to the solution. I think yours was probably more efficient and probably more elegant but I went with what I could make work. My solution was to create the proc formats (as you saw) and then simply call them as formats at the end. I'm not sure why this worked but it did. You can see the f=earned_level. and f=less_assist. at the end and that seemed to create both the "lookup" columns I need. As I said, I'm not if this was the best solution but it seems to work and I guess that's a good thing.

Again, thanks very much for your help.

	table id_number = 'ID ' * name /*ALL={label='Total Points 1'*/ /*s=[just=center]}*/,
			(work_date = 'Date' ALL='Total Points' f=earned_level. f=less_assist.)
			* (pointspaid = ' ') / misstext = ' ' style=[font_size=2] box = {label = ' '};

[Jeff_DOC]

Oh. And thank you very much for the "code block" suggestion. I'll use that in the future.