PROC TABULATE

[sayanapex06]

Hi All,

I need an help creating the following table :

	SSO	BUC	ADN	Count for BUC/ADN	Grand Total
SSOs Not Actioned because they are Duplicated in the Input File					2
	100001155	SM0032	3465210101243WCVE56
	100001155	SM0300	3465210101243WCVE56

with variables as description = SSOs Not Actioned because they are Duplicated in the Input File 

sso buc ADN and count 

Please help me in doing it.

So far the code I used :

PROC TABULATE DATA = REPORT_ALL MISSING F=3. S=[CELLWIDTH=3000 JUST=C];
KEYLEVEL SUM = ' ' ;
CLASS DESCRIPTION
SSO
BUC
ADN ;
VAR COUNT ;
TABLES DESCRIPTION=' ' ALL ='TOTAL',
(SSO=' ')*(BUC ADN COUNT)
/BOX = "REPORT ON THE NUMBER OF SSO_IDs FOR EACH CATEGORY AS FOLLOWS :" ;

Its not coming in the format required. 

And I cannot use more than three dimensions for a table.

Please help with the correct way.

Would be thankful,

Sayan

[ballardw]

Do you mean that you only want a table where the variable SSO has a value of "not actioned"? Or is this a value of the Description variable? Typically to exclude records from the tabulate results you would use a WHERE statement. As a brief example if I wanted to exclude some ages from a Proc tabulate of the sashelp.class data set I might do this to exclude ages 14 and larger:

proc tabulate data=sashelp.class;
   where age < 14;
   class sex age;
   table sex, age all='total'
   ;
run;

So to exclude records with specific value you might have something like:

Where description ne "Value to exclude";

And can you be more explicit about the impact of "duplicated in the input file" actually is?

Best would be to provide a small sample of actual data and what the tabulate result for that example data would be.

Provide records that would be excluded.

It would also help to show what your currently getting as a result and then describe exactly how "Its not coming in the format required."

[sayanapex06]

No value is getting excluded..
I want the table COLUMNS to be like :

Description Sso BUC ADN COUNT(SSO) TOTAL COUNT

[ballardw]

---

@sayanapex06 wrote:
No value is getting excluded..
I want the table COLUMNS to be like :

Description Sso BUC ADN COUNT(SSO) TOTAL COUNT

---

Really , provide some example data and what the actual output desired should be that you can do by hand. Since you are putting any statistics and COUNT is NOT a tabulate statistic, or how want your Count variable used it really is not clear at all what you are trying to accomplish without a concrete example. You example code shows things nested:

(SSO=' ')*(BUC ADN COUNT) which forces BUC ADN variables to appear UNDER SSO in the columns. If you don't want that remove the * and likely the ().

Perhaps start with TWO variables instead of all 4 class variables so you do not have to provide as much example data..

[Cynthia_sas]

Hi:

  Let's take a step back and talk about PROC TABULATE structure. For example:

with the exact same rows from SASHELP.SHOES, notice the huge difference between the structure of the above 2 tables with regard to the REGION and PRODUCT variables. With example #1, the REGION values are going down the rows and the PRODUCT values are going across the columns. That is because only REGION and ALL are placed in the row dimension. Once the comma is placed in the TABLE statement, that forces TABLE #1 to have unique values for PRODUCT used as column headers with SALES and N nested underneath each unique value of PRODUCT. Then, the final "Grand Total" is produced by using ALL in the column dimension.

TABLE #2 is definitely different in structure. TABLE #2 uses the * operator to cross or nest the row values for the CLASS variables REGION and PRODUCT. So with

TABLE region*product all,

for the row dimension,  each unique value of PRODUCT was nested within each unique value for REGION. From the small sample of desired output that was posted, and without data to test, it seems to me that the desired output you posted looks more like Table #2 than Table #1.

  The CLASS and VAR statement just tell TABULATE how a variable is going to be used in creation of the tabular output. It is the TABLE statement that is responsible for defining the structure of the output.

  Here are some good papers and a book recommendation for PROC TABULATE:

"PROC TABULATE: A Getting Started Tutorial" http://support.sas.com/resources/papers/proceedings11/260-2011.pdf
 
"Speaking Klingon: A translator’s guide to PROC TABULATE"  http://www2.sas.com/proceedings/sugi30/258-30.pdf
 
"Using Multi-label Formats to Create Subtotals in PROC TABULATE" http://support.sas.com/resources/papers/proceedings11/086-2011.pdf
 
"Big Money Proc TABULATE Generating Great Reports with the TABULATE Procedure" http://support.sas.com/resources/papers/proceedings12/256-2012.pdf
 
Tech Support white paper on Style elements with REPORT and TABULATE https://support.sas.com/resources/papers/stylesinprocs.pdf
 
Book: PROC TABULATE by Example https://www.sas.com/store/books/categories/examples/proc-tabulate-by-example-second-edition/prodBK_6...

  Hope this helps,

Cynthia

[sayanapex06]

Thanks a lot Cynthia for the support.

I chose to use Proc report instead, as Proc Tabulate was not able to meet my needs.

PROC REPORT DATA = REPORT_ALL NOFS HEADLINE HEADSKIP
STYLE (REPORT) = [FRAME = BOX] ;

TITLE1 "##############################################################" ;
TITLE2 "## DIVESTIT : REPORT OF ALL SSO IDS TODAY ##" ;
TITLE3 "##############################################################" ;

COLUMN DESCRIPTION SSO BUC ADN COUNT ;
DEFINE DESCRIPTION / GROUP WIDTH=100 ;
DEFINE SSO / ORDER WIDTH=10 ;
DEFINE BUC / DISPLAY WIDTH=6 ;
DEFINE ADN / DISPLAY WIDTH=75 ;
DEFINE COUNT / ANALYSIS SUM WIDTH=20 'COUNT FOR BUC/ADN' ;

BREAK AFTER DESCRIPTION / OL UL SKIP SUMMARIZE SUPPRESS ;
RBREAK AFTER / DOL DUL SKIP SUMMARIZE ;
COMPUTE AFTER ;
TYPE = 'TOTAL' ;
ENDCOMP ;
RUN ;

The code I used is above :

The output I got is : attached 

I want :

where the 17 is calculated, something to add a comment as 'total of all' .

COuld you help me with this.

[Cynthia_sas]

Hi:

  I'm sorry, when I look at your output, it looks like LISTING output and I just don't see anything that makes sense. For example, you said "where the 17 is calculated, something to add a comment as 'total of all' " But when I look at the preview the lines wrap oddly and I don't see anyplace where "17" is calculated. And, what is a comment? Here's what I see -- a partial screen shot:

In your code, your use of options is inconsistent with the Output Delivery System. Options like DOL, DUL, HEADLINE, HEADSKIP, WIDTH=, OL, UL are out of sync with your use of

PROC REPORT DATA = REPORT_ALL NOFS HEADLINE HEADSKIP
STYLE (REPORT) = [FRAME = BOX] ;

the STYLE(REPORT) option, either you are using ODS, like PDF, HTML or RTF or you are using the LISTING output, in which case, I'd expect to have Linesize and Pagesize options set wider and to landscape.

Without data to test, it is nearly impossible to answer your question, since I don't see where any "17" is being calculated and I can hardly read your Preview in the TXT file, and it looks worse when I download it than when I use the Preview window.

  Here's an example using SASHELP.CLASS....where I've added what I think can be considered a comment at the bottom of the report. Keep in mind that my statistics don't make a lot of sense, since adding up height is sort of silly.

Hope this gives you an idea of how the LINE statement works. I chose to put the string "Total" in the NAME column, since NAME is a character variable.

Cynthia