topic Re: Proc Tabulate: Adding lookup values to ALL totals in SAS Programming

Proc Tabulate: Adding lookup values to ALL totals

Jeff_DOC — Tue, 21 Dec 2021 16:59:16 GMT

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	Total Points
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	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	396
200	NAME 2	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14									196
200	NAME 2	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
400	NAME 4	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	Total Points	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	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
200	NAME 2	14	14	14	14	14	14	14	14	14	14	14	14	14	14	14									196	$50.00	$47.50
200	NAME 2	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
400	NAME 4	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

Re: Proc Tabulate: Adding lookup values to ALL totals

Reeza — Tue, 21 Dec 2021 17:06:45 GMT

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

Re: Proc Tabulate: Adding lookup values to ALL totals

Jeff_DOC — Tue, 21 Dec 2021 18:44:24 GMT

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

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;

Re: Proc Tabulate: Adding lookup values to ALL totals

Reeza — Tue, 21 Dec 2021 19:34:01 GMT

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.

Re: Proc Tabulate: Adding lookup values to ALL totals

Reeza — Tue, 21 Dec 2021 19:34:54 GMT

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

Re: Proc Tabulate: Adding lookup values to ALL totals

Jeff_DOC — Tue, 21 Dec 2021 20:01:48 GMT

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

Re: Proc Tabulate: Adding lookup values to ALL totals

Reeza — Tue, 21 Dec 2021 20:03:40 GMT

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

Re: Proc Tabulate: Adding lookup values to ALL totals

Jeff_DOC — Tue, 21 Dec 2021 20:10:30 GMT

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.

Re: Proc Tabulate: Adding lookup values to ALL totals

Reeza — Tue, 21 Dec 2021 20:46:20 GMT

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.

Re: Proc Tabulate: Adding lookup values to ALL totals

Jeff_DOC — Mon, 27 Dec 2021 18:21:48 GMT

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 = ' '};

Re: Proc Tabulate: Adding lookup values to ALL totals

Jeff_DOC — Mon, 27 Dec 2021 18:24:26 GMT

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