BookmarkSubscribeRSS Feed
huikeng_sas
SAS Employee
Hi,
I ran the following codes:

PROC TABULATE
DATA=WORK.REPORT_DATA
FORMAT=COMMA16.0
;

VAR DLY MTD YTD;
CLASS "Division Name"n / ORDER=UNFORMATTED MISSING;
CLASS "Desk Name"n / ORDER=UNFORMATTED MISSING MLF;
CLASS "Sub-Desk Name"n / ORDER=UNFORMATTED MISSING;
TABLE /* Page Dimension */
"Division Name"n ="Division Name :" *
"Desk Name"n = "Desk Name : ",
/* Row Dimension */
"Sub-Desk Name"n={LABEL=''}
ALL={LABEL='Desk Total'},
/* Column Dimension */
DLY*
Sum={LABEL=''}
MTD*
Sum={LABEL=''}
YTD*
Sum={LABEL=''}
/* Table Options */
/ BOX={LABEL='Dealer'} ;
;

RUN;

Currently, the Division Name and Desk Name are seperated by comma in the oputput. How can I split the Divsion Name and Desk Name into 2 lines?

Thanks!
4 REPLIES 4
ArtC
Rhodochrosite | Level 12
I imagine that you are also getting a number of errors in the LOG.
1) there is no order=unformatted
2) Beware of missmatched quotes - these are getting you in trouble here
3) You must have the system option validvarname=any turned on to use the nonstandard variable name structures.
4) what are you trying to do with the ={label="} ?? this gives you the mismatched quote and is not valid syntax.
huikeng_sas
SAS Employee
Hi Art,
Thanks for your reply.
There is no syntax error in the log as I have generated these codes and run them within EG 🙂

My query is I would like to know if I can seperate the Division Name and Desk Name into 2 lines in my output. Currently, they are displayed on one line, sperated by a comma.

Thanks!
ArtC
Rhodochrosite | Level 12
Now I better understand the question, and I learned some TABULATE syntax too!

I noticed that in the LISTING destination the word AND is used as in:
a, b, and c
for the page label. In PDF each is comma separated as in:
a, b, c
This is demonstrated in the following code, which mimics yours:
[pre]ods pdf file='c:\temp\shoes.pdf' style=default;
PROC TABULATE DATA=sashelp.shoes;
VAR sales;
CLASS region product subsidiary;
TABLE region*product,
subsidiary,
sales*sum;
RUN;
ods pdf close;[/pre]

I have not yet found a style attribute that will solve the problem however.
Cynthia_sas
SAS Super FREQ
Hi:
ODS allows the syntax:
[pre]
somevar={LABEL=' '}
[/pre]

as a way of applying a style and a label in the table statement. Most of the time, I only use syntax like this in the BOX option, but it looks like EG is being consistent with every relabeled variable. It also looks like EG is using quote-quote
[pre]
LABEL=''
[/pre]

as a way to suppress the label.

The confusion for me came in when I was just looking at the post and thought that you had an unmatched double quote in your code, because the two quotes without a space look like an unmatched double quote to my eyes -- especially in a proportional font:
'' quote-quote (with no space)
" one double quote by itself
' ' quote-space-quote (single quotes)
" " dquote-space-dquote (double quotes)

So I had to paste your code into Notepad and make the font REALLY big, to see that you had posted 2 single quotes next to each other and not an unmatched double quote. When I suppress the label, I like to use quote-space-quote
[pre]
LABEL=' '
[/pre]

which in most instances that I am aware of gives the same result as quote-quote and for my eyes, is easier to read. Also, as you can see from the above posting, when you compare the "regular font" code snippet with a "fixed pitch" font code snippet, the quote usage is clearer in the fixed pitch snippet. I used the [pre] and [/pre] tags around my code in order to maintain indenting and spacing; those tags also have the side effect of making the quotes easier to read.

However, all that aside, let's go back to your question, which was possibly 2 questions:
1) how to get Division and Desk name on separate lines and
2) what to do with that comma

The code at the end of this post shows a few different possibilities:
1) use ODS ESCAPECHAR to insert a "line feed" or "carriage return" into the page dimension string. In my code, this is represented by the
[pre]
ODS ESCAPECHAR='^';

and use of ^n in the string:
"^nDesk Name: ",
[/pre]

However, even though you get 2 lines now, you will still see a comma which isn't horrible, but may not be what you want.

Next, since you're not using percentages or anything that might be thrown off by BY groups, you could just use BY group processing, as shown in example 2 output. This has the advantage of using standard BY group processing and there is no comma, but you get the output all on 1 line and there is a default = sign in the BY variable string. Of course, using ODS ESCAPECHAR, you can get around the BY variables being all on 1 line, but the = signs are immutable.

The good news is that, once you have gone into the world of BY group processing, you can now take advantage of #BYVAL/#BYVAR capability in the SAS TITLE. This is pretty cool because you can absolutely control exactly what you want to see for the text strings(for example, colons instead of = sign). You don't need ODS ESCAPECHAR because every title gets a new line.

So you have 3 choices, depending on what you want to do and how you want the output to look. If you are using EG to generate the TABULATE code, there is nothing automatic you can do -- any of these approaches would be a modification to the EG generated code. Do note that #2 and #3 both require a sorted dataset in order to use BY group processing -- so you may want to factor that into your EG task or process flow, too.

cynthia
[pre]
** Limit observations;
proc sort data=sashelp.prdsale out=prdsale;
by division country;
where division = 'EDUCATION' and
country in ('CANADA', 'GERMANY');
run;

ods listing close;
ods html file='c:\temp\forum_tab.html' style=sasweb;
**1) Use ODS ESCAPECHAR to put LINE FEED before Desk Name label, will still get comma;
ods escapechar='^';
PROC TABULATE DATA=prdsale FORMAT=COMMA16.0;
title '1) Using Page Dimension with ESCAPECHAR -- still get comma';
VAR actual predict;
CLASS division / ORDER=UNFORMATTED MISSING;
CLASS country / ORDER=UNFORMATTED MISSING;
CLASS prodtype/ ORDER=UNFORMATTED MISSING;
TABLE division ="Division Name:" *
country = "^nDesk Name: ",

prodtype={LABEL=' '} ALL={LABEL='Desk Total'},

actual*Sum={LABEL=' '}
predict*Sum={LABEL=' '}

/ BOX={LABEL='1) Page Dim'} ;
RUN;

**2) Switch to BY group processing, which may work in this situation;
** but you will get = sign in BY val string, and BY val string is on 1 line;
** unless you use ODS ESCAPECHAR in label for one of the BY vars.;
PROC TABULATE DATA=prdsale FORMAT=COMMA16.0;
by division country;

title '2) Using By Group Instead of Page Dimension';
VAR actual predict;
CLASS division / ORDER=UNFORMATTED MISSING;
CLASS country / ORDER=UNFORMATTED MISSING;
CLASS prodtype/ ORDER=UNFORMATTED MISSING;
TABLE prodtype={LABEL=' '} ALL={LABEL='Desk Total'},

actual*Sum={LABEL=' '}
predict*Sum={LABEL=' '}

/ BOX={LABEL='2) By Grp'} ;
label country = '^nDesk Name';
RUN;

**3) turn off BYLINE and put information in TITLE and make title a bit smaller;
** This means using #BYVAL syntax in TITLE statement and using option to turn off;
** the automatic by line behavior.;
options nobyline;
PROC TABULATE DATA=prdsale FORMAT=COMMA16.0;
by division country;

title h=10pt '3) Division Name: #byval1';
title2 h=10pt 'Desk Name: #byval2';
VAR actual predict;
CLASS division / ORDER=UNFORMATTED MISSING;
CLASS country / ORDER=UNFORMATTED MISSING;
CLASS prodtype/ ORDER=UNFORMATTED MISSING;
TABLE prodtype={LABEL=' '} ALL={LABEL='Desk Total'},

actual*Sum={LABEL=' '}
predict*Sum={LABEL=' '}

/ BOX={LABEL='3) #BYVAL Example'} ;
RUN;
ods _all_ close;
options byline;
title;

[/pre]

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 1236 views
  • 0 likes
  • 3 in conversation