The SAS Output Delivery System and reporting techniques

PROC TABULATE:- QUERY ON BOX= OPTION

Reply
Occasional Contributor
Posts: 15

PROC TABULATE:- QUERY ON BOX= OPTION

SUMMARY

BOX= must display value other that _page_ or byval options..

DESCRIPTION
Hi Experts,

I've a four dimension proc tabulate display. (There are two possible values for first dimension and many possible for the third dimension and so forth)

I use 'by' option on the second dimension and also use 'condense' option so that in report so generated there will be two tabulate reports created on a single page. Each tabulation will correspond to theone of the two possible values of first dimensions, and the number of pages will be / and correspond to the second dimension where many values are possble.

Until here everything works fine. However the reort so generated, the dispaly on top left corner which correspond to 'BOX=' option is blank.

I want this to be filled with value of the second dimension --> (which I have mention as the by val) . I used BOX=_PAGE_ option, but then it uses the value of the first dimension in the box whcih I dont want.

Could any of you experts help me out in this.

In short, I want the second dimension value to eb specified in the BOX when I have a four dimension tabulation.

Thanks in advance.

SAS Super FREQ
Posts: 8,743

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi:

  I'm having a hard time visualizing your code. PROC TABULATE only has 3 dimensions: PAGE, ROW and COLUMN. You can have more than 1 variable in 1 dimension, though. Your BY variable is your BY variable -- and while it may look like another dimension, it's a BY variable and has different behavior than the PAGE dimension. BY variable values, can be put into your SAS TITLE using #BYVAL, #BYVAR  and #BYLINE.

  

  You can put any text you want into the BOX= option, using BOX='text string' or BOX=variable. BOX=_PAGE_ only works as you describe, whatever you have listed in the PAGE dimension goes into the box. When you have something like this:

  BY BY-VAR;  /* <---this is a statement, not an option.  */

  TABLE PAGE-DIMENSION,

              ROW-DIMENSION,

              COLUMN-DIMENSION;

your BY statement is specifying a  BY variable.

So, what I'm having a hard time visualizing or understanding is whether your BY variable is also listed in your TABLE statement and if so, where it is listed. You say you have the BY variable listed using the second dimension....do you mean that the BY variable is the same as the ROW dimension variable?

Can you post your code? If you can't post your code or a sample of your data, can you replicate your TABULATE using SASHELP.CARS or SASHELP.SHOES and then post that code? That would be code that anybody else could run. Otherwise, it is hard to provide more concrete advice.

cynthia

Occasional Contributor
Posts: 15

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi Cynthia,

Thanks for your feedback.

I've attached some sample data with tis post. Sample code is below

proc format;

value qwer;

....

....

.< some values ...>>

....

run;

proc tabulate data=abcd out=new s=[background=qwer.];

   var salary1 salary2 salary3 salary4 salary5;

   class message / order = unformatted descending missing;

   class division/ order = data missing;

   class department / order = unformatted missing;

   class period / order= unformatted missing;

   class tabble /  order = unformatted missing;

table

   tabble=' ' ,

   division=' ' *

   salary1 salary2 salary3 salary4 salary5,

   department=' ' *

   period=' ' *

   (

    message=' ' *

    sum=' ' ) / condense BOX=_page_;

  by division;

run;

Hope the sample data and sample code explains the issue I'm talking about....

Thanks,

Sarath

Occasional Contributor
Posts: 15

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi Cynthia,

In addition to what I've explained above, I also want the following two things to be done....

I've speciifed a colour format on the tabulate report. This is applied to the entire report for all VAR values corresponding to all class values . I dont want this coloring to happen to the whole report.

I only want the colouring to happen for a VAR value corresponding to ONE particular CLASS value and in only one of the tabulation.

In the report so generated using the sample code and sample data, you will actually see in one page two tabulations - one each for the two possible values of the outermost (fourth dimension) class value. And you can see many such pages, each of which correspond to the next dimension which has been specified in the by statement.

Now my requirement is that

1. The colouring must happen only on the second tabultaion in each such page, and also

2. It must happen only to VAR values of ONE kind of class variable.

i.e in teh sample data attached , the colouring must eb there only for  VAR values (viz) SALARy1 - SALARy5 , corresponding to class value 'AD' corresponding to variable PERIOD, of class value '2' corresponding to variable 'TABBLE'

Thanks,

Sarath

SAS Super FREQ
Posts: 8,743

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi:

  OK...well, you didn't post your format code, so I don't know what the formats are or what you're trying to do. I did have observations/comments about your code, though (format comment is #5).

  I have these observations about your code. Let me reiterate an earlier statement. There is no 4th dimension in PROC TABULATE. There is only PAGE, ROW and COLUMN -- 3 dimensions. The BY statement may appear to add a 4th dimension -- but it is really providing logical "break" behavior. Every BY group is treated as 1 "group" of rows. I do not know what you are calling the 4th dimension. Especially since you have DIVISION as both a ROW dimension and a BY variable.

  So, I'm further confused. Here's what I see/question/comment:

1) You are crossing DIVISION with SALARY1, but not with the other SALARY variables.  Do you really mean to have this:

division=' ' *   salary1 salary2 salary3 salary4 salary5,

which only crosses the value for DIVISION with the ROW header for SALARY1. If you did something like  this:

division=' ' * ( salary1 salary2 salary3 salary4 salary5),

Then you would have a row with the DIVISION value that spanned ALL the headers for the SALARY variables. I think that would look nicer.         


2) Do you want DIVISION and TABBLE in the BOX or just DIVISION? LIke this:

Div=70 Tab=1 Div=70 Tab=2 OR, you you want just the 70 and the 1 or 2 or do you want DIVISION ONLY Do you want Division=70 or Division=79 or do you just want the 70 or 79?

 

3) What is your destination of interest? LISTING, RTF, PDF, HTML? Where are your ODS statements. What is your destination?

 

4) I don't see that DIVISION really adds anything to your TABLE statement, the way it is currently coded. Have you tried your code with DIVISION in the BY statement but NOT in the TABLE statement? Just because you use BY DIVISION in your code, means that DIVISION doesn't have to be in the TABLE statement. Something like this:

table

   tabble=' ' ,  /* PAGE dim */

   (salary1 salary2 salary3 salary4 salary5),  /* ROW dim */

   department=' '*period=' '*(message=' '*sum=' ') /* COL dim */

   / condense BOX=_page_;

   by division;   /* BY statement */

5) When you use a STYLE= override in the PROC TABULATE statement, it ONLY touches the calculated DATA cells. It does not touch the CLASS variable values. So I was concerned when I saw your format on the TABULATE statement because 1) you didn't show the format and 2) you didn't show the desire results, which led me to believe that your format might not be working. Now, you say that "This is applied to the entire report for all VAR values corresponding to all class values . I dont want this coloring to happen to the whole report." Where you put the format has the implied impact of formatting all the calculated data cells -- or the whole report -- that's what you asked for in your code. And I don't understand what you mean by "all VAR values corresponding to all class values"...do you mean if TABBLE=1 and DEPARTMENT=1 then you want the color coding, but if TABBLE=2 and DEPARTMENT=1 then you do NOT want color coding? PROC TABULATE does not perform this kind of "trafficlighting" -- this is usually the type of trafficlighting that you perform with PROC REPORT.

6) Not sure why or what purpose the OUT= is serving. What further processing are you going to do with the OUTPUT dataset? The BOX and/or BY information is not reflected in the output dataset directly.

There might be a couple of different ways to do what you want, but it really depends on your answer to #2, whether you want DIVISION and TABBLE in the BOX or just DIVISION. Have you done any kind of work with SAS Macro processing or macro variables? If you had something like this:

   BOX="&divstring";

then you could call your basic TABULATE for each BY group and change  the value for &DIVSTRING with each call.

  So I hope that some of my observations will help you clarify your question and what you want to do.

cynthia

Occasional Contributor
Posts: 15

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi Cynthia,

Thanks for your feedback.

I've attached the complete code and the report so generated out of it with this post. Could you please have a look at it.

In the report you will see that in each page, there are two tabulations. Each one corresponds to distinct value of column TABBLE in the input.

I want the colouring to happen for only 'AD' column in the seond tabulation in each page. Is such a formating possible at all?

I hope the sample report attached clarifies my requirement. This was created out of the sample data which I send earler.

Thanks in advance.

Sarath

Attachment
Attachment
Occasional Contributor
Posts: 15

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi Cynthia,

Additionaly , if this kind of coloring - traffic lighting is not posible in Tabulate. How can the same be implemented in Report?

I need the structure of the report exactly the same as in the sample html report attached. But I'm not sure if the structure as attached can be re-created exactly in Report procedure- (the two tabulations in one page) , could you please help in this regard.

Thanks in advance for your advice and help.

regards,

Sarath

SAS Super FREQ
Posts: 8,743

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi:

  You didn't say anything about #1 -- did you even try this TABLE statement:

division=' ' * ( salary1 salary2 salary3 salary4 salary5),

  Also, you never answered question #2 -- do you still want to see the DIVISION value on the same row as SALARY1 -and- in the BOX area? You never said whether you wanted DIVISION and TABBLE in the BOX or just DIVISION. Where will TABBLE go if you only put DIVISION in the BOX? Initially, your whole purpose for making the post was that you wanted DIVISION value in the BOX.

  You did show your destination of interest as HTML. I wonder what your version of SAS is -- 9.1.3 or 9.2? I see that you posted a copy of the default style. That implies that you are not using SAS 9.3.


  Did you try the alternate TABLE statement shown in #4?

  Thanks for sending all your code, so I can see the format. Did you do any research on TABULATE to see what other places allow the use of the STYLE= override. Is this requirement -- to only have TRAFFICLIGHTING in the cells where PERIOD=AD. The CLASS variable PERIOD is responsible for making the AD column. AD is a value for the PERIOD variable. Other values for PERIOD are 2000, 2010 and BC. Generally, PROC TABULATE does not have visibility of the CLASS variable in the crossing, so you can ask for PERIOD=AD to have trafficlighting, but the other values of PERIOD not to have trafficlighting. There's no place for you to position your format to make that happen in TABULATE. And in PROC REPORT, you would have to use CALL DEFINE and ACROSS and ABSOLUTE column numbers, but then you would lose the "BOX" area that TABULATE has and I thought that was the whole point of your original posting.

  Have you ever used PROC REPORT? Have you ever used SAS Macro processing (you didn't answer that question either). But it really comes down to what is more important - -getting DIVISION in the BOX area and keeping your 2 logical pages for each DIVISION or getting trafficlighting for only PERIOD=AD????

  So still some questions unanswed by from previous post and more question in this post. I did have yet another question -- is this what your data originally looked like, or is this your data as structured by a previous procedure (such as TABULATE or MEANS)??

cynthia


  You never explained what the purpose was for OUT=.

Occasional Contributor
Posts: 15

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi ,

Thanke for your reply. Sorry for the missing info. Here are the same

1 and 4) Yes I did try the code division=' ' * (salary1, ....salary5). Then the report generated was neat than before with the division appearing left side of the salaries 1 - 5.

2. I want to see the DIVISION appear in the BOX area not asie the salary 1 - salary 2. In just above point, as stated teh division appear as a column left side of the salaries. I dont need this, instead i want it in the box -> This was my original issue :-)

3. My destination . i just created for a sampel report. It can either be html or excel which is decdied dynamically. and I'm using 9.2

4. The need is both - two tabulations in one page and colouring of only one of the tabulations and for only period=AD. I've not used report as much as i have used tabulation and yes I've used macros and the out= is just to store the data for reference/test etc.

regards

SAS Super FREQ
Posts: 8,743

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Yes, I understand that your original question was how to get DIVISION in the box. But you still haven't answered about TABBLE -- your PAGE dimension -- do you want it ALSO in the box? If you don't put DIVISION and TABBLE in the box, then TABBLE value has to be someplace .. normally, it will sit on "top" of the box.

Also, your color coding scheme is not clear (see screenshot) -- you call AD a "column" but it's not a "column" not in the Excel sense of "column" AD (like BC, like 2000, like 2010 is the VALUE of the column or variable PERIOD). So AD cannot be treated separately from BC, or 2000 or 2010  -- not in TABULATE. So my question is: Even if you CAN get DIVISION in the BOX area, is that of any use to you with TABULATE if you cannot get the color coding you want?

cynthia


divsion_box_tabble_outside.png
Occasional Contributor
Posts: 15

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi Cynthia,

No, i do not want the page dimension also in the box. I only need the division in the box. In my curernt code, since i could not put division in the box i put tabble value in the box and used nobyline so that the division is not visible at page breaks....

Ans yes since i cannot use the coloring as per my requirements with this perticulat tabulate code. it makes it useles to further try using prc tabulate...

I'm moving over to another way may be proc template to see if its possible....

Thanks for your advice...

Regrdas,

Sarath

SAS Super FREQ
Posts: 8,743

Re: PROC TABULATE:- QUERY ON BOX= OPTION

Hi:

Hmmmm, well, I'd hate to see you spin your wheels on PROC TEMPLATE for styles -- it's totally not possible to do anything to your table with a style template. So when you talk about TEMPLATE, you could mean a custom table template -- which I'm having a hard time figuring out how it would work.

  There are a few things about PROC TEMPLATE for tables that might steer you away - first of all, it is not a summary procedure -- so if you need your data summarized by any of the groups, you will have to pre-summarize. Also, I am not sure that you can get the level of cross-tabulation that you want. You can create nested headers, but that is not the same as a full cross-tabulation of calculated values into groups.

   I'd recommend looking at PROC REPORT.

cynthia

SAS Super FREQ
Posts: 8,743

Re: PROC TABULATE:- QUERY ON BOX= OPTION


Hi:

  I mocked up 3 different possibilities with PROC REPORT. It does NOT have a BOX area like TABULATE. So the 3 screenshots are what your report would look like -- for 1 division -- for example...#1 and #2 show how the Division value can be put on the report and #3 shows the color coding for just the AD values of PERIOD. The #3 approach will work no matter if you choose #1 look or #2 look for DIVISION values. I created the report for 1 DIVISION, using BY TABBLE in the REPORT code.

  One difference between REPORT and TABULATE is that REPORT does not show a BOX at the intersection of row & column headers, like TABULATE, because REPORT builds the output report differently from TABULATE. This is closer to what you want than you could get with PROC TEMPLATE. REPORT does require a slightly different structure to your DATA.

cynthia


use_mac_var_div2.pngcolor_coding_3.pnguse_compute_before1.png
Ask a Question
Discussion stats
  • 12 replies
  • 1002 views
  • 6 likes
  • 2 in conversation