Issues with using proc tabulate and ods pdf to output data table into pdf file ...

Reply
Contributor
Posts: 30

Issues with using proc tabulate and ods pdf to output data table into pdf file ...

I use ODS PDF and PROC Tabulate to output my data into a PDF file.

Below are issues I have with my code:

    1. At the top of the output, there is always a line "The SAS System". How can I get rid of it?

    2. I have two row class variables. The row classes are Country and Year. The Country are 'Cananda' & 'USA'. For 'Cananda' I have 2013 & 2012 and for 'USA' I have 2014 & 2013 & 2012. My data is descending order in year. However in my PDF file, the year of 'Canada' are in descending order such as 2013 & 2012. But in 'USA', the order of year is 2013, 2012 & 2014. The 2014 is at the bottom instead of at the top. How can I fix this?

    3. My column variable is sales. I have another variable weight. I want sales to be bold if weight more than 1000, sales to be Grey Italic if weight between 500 and 1000, and sales to be supressed if weight is less than 500. How can I do that?

    4. I want my output table is in landscape orientation instead of being portrait, how can I do that?

    Thanks.

Jian Zhang

Super User
Posts: 19,057

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

1. Insert a blank title statement

title;

2.  Look at the order= option in the class statement to see what your options are.

Post sample code for more assistance.

3. Conditional highlighting - there are a ton of papers on this, not very basic but not hard. Again post some code/sample data so the answers are relevant to you.

4. Try the following for landscape.

options orientation='landscape';

Contributor
Posts: 30

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

Hi Reeza,

    Thanks for your help. Below is my data and code:

ODS listing close;

ODS PDF FILE = "Report.pdf";

Data Report;

INPUT Country $ Year Sales wt;

DATALINES;

Canada 2013 1000.00  250

Canada 2012 2000.00  750

USA    2014 3000.00 1000

USA    2013 2000.00  750

USA    2012 1000.00  250

;

run;

Proc Sort Data = Report; by Country descending Year; Run;

Options Orientation = 'LandScape';

title;

Proc Tabulate Data = Report Style=[font_size=0.5 background=white];

class Country Year / Order=Data Style=[font_size=0.5 background=white];

classlevel Country Year / Style=[font_size=0.5 background=white];

var Sales / Style=[font_size=0.5 background=white];

Table Country = ' ' * Year = ' ',  Sales = 'Sales' * f=10.2  / Condense NoContinued ROW = FLOAT box=[style=[background=white]] style=[frame=void rules=rows background=white];

Run;

ODS PDF CLOSE;

ODS listing;

    Your solution to 1. and 4. do not work.

    Thanks.

Jian

Contributor
Posts: 30

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

Hi Reeza,

    Your solution to 1 & 4 are right. It works now.

    For 2, I want country in ascending order and year in descending order, how can I do that?

    For 3, how can I do the conditional highlighting? The highlighting of sales does not depend on the value of sales but depend on the value of wt.

    Thanks a lot for your help.

Jian

Super User
Posts: 19,057

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

For 2, try using different class statements with different order options. I'm not exactly sure what you'll need.

class country/order=formatted;

class country/order=data;

3 is beyond me.  A quick search shows that highlighting based on another variable is best done via proc report rather than tabulate, but I'm not certain.

Super User
Posts: 9,865

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

For 2,

Proc Tabulate Data = Report Style=[font_size=0.5 background=white];

class Country  / Order=internal  Style=[font_size=0.5 background=white];

class  Year / Order=internal descending Style=[font_size=0.5 background=white];

classlev Country Year / Style=[font_size=0.5 background=white];

var Sales / Style=[font_size=0.5 background=white];

Table Country = ' ' * Year = ' ',  Sales = 'Sales' * f=10.2  / Condense NoContinued ROW = FLOAT box=[style=[background=white]] style=[frame=void rules=rows background=white];

Run;

For 3, Better use proc report.  Post it at ODS and Base Reporting  Cynthia could offer you help.

Here is a workaround for proc tabulate , but I really suggest you to change to use proc report .

 
ODS listing close;
 
ODS PDF FILE = "Report.pdf";
ods escapechar='~';
Data Report;
INPUT Country $ Year Sales wt;
DATALINES;
Canada 2013 1000.00  250
Canada 2012 2000.00  750
USA    2014 3000.00 1000
USA    2013 2000.00  750
USA    2012 1000.00  250
;
run;
 
proc sort data=report(keep=wt sales) out=x nodupkey;by wt sales;run;
data x;
 set x(rename=(wt=start));
 retain fmtname 'fmt' type 'n';
 length label $ 40;
 if start gt 500 then label=cats('~S={background=darkblue}',sales);
  else label=cats('~S={background=red}',sales);
run;
proc format library=work cntlin=x;run;
Options Orientation =LandScape;
 
title;

 Proc Tabulate Data = Report Style=[font_size=0.5 background=white];
class Country  / Order=internal  Style=[font_size=0.5 background=white];
class  Year / Order=internal descending Style=[font_size=0.5 background=white];
classlev Country Year / Style=[font_size=0.5 background=white];
var wt / Style=[font_size=0.5 background=white];
Table Country = ' ' * Year = ' ',  wt = 'Sales' * f=fmt.  / Condense NoContinued ROW = FLOAT box=[style=[background=white]] style=[frame=void rules=rows background=white];
Run;
 
ODS PDF CLOSE;
 
ODS listing;


Xia Keshan

Message was edited by: xia keshan

Contributor
Posts: 30

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

Hi Xia,

    I want suppress columns if this column's weight is too small, i.e., I want to replace its value with a tiny '-' sign. How can do it? font_weight = suppressed or font_style = suppressed? Thanks.

Jian

SAS Super FREQ
Posts: 8,818

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

Hi:

  Font_weight=suppressed is NOT valid as an attribute value. The font_weight is a value like BOLD or MEDIUM (or Demibold if you have the font) -- did you look in the documentation for style attributes? Here's the appropriate place in the documentation:

SAS(R) 9.4 Output Delivery System: User's Guide, Third Edition

  It is hard to suppress values in TABULATE based on the value of another variable. This is generally something that you do with PROC REPORT in a COMPUTE block. Have you looked in the PROC REPORT documentation? We also cover this topic in our Report Writing 1 class SAS Training in the U.S. -- SAS Report Writing 1: Essentials

cynthia

Super User
Posts: 9,865

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

Yeah. you can do it by my code. if wt less than 250 will be suppress.But it is really clumsy. I really suggest you to follow Cynthia's advice . use proc report.

ODS listing close;

ODS PDF FILE = "Report.pdf";

ods escapechar='~';

Data Report;

INPUT Country $ Year Sales wt;

DATALINES;

Canada 2013 1000.00  250

Canada 2012 2000.00  750

USA    2014 3000.00 1000

USA    2013 2000.00  750

USA    2012 1000.00  250

;

run;

proc sort data=report(keep=wt sales) out=x nodupkey;by wt sales;run;

data x;

set x(rename=(wt=start));

retain fmtname 'fmt' type 'n';

length label $ 40;

if start gt 250 then label=cats('~S={background=green}',sales);

  else label=cats('~S={background=red}','-');

run;

proc format library=work cntlin=x;run;

Options Orientation =LandScape;

title;

Proc Tabulate Data = Report Style=[font_size=0.5 background=white];

class Country  / Order=internal  Style=[font_size=0.5 background=white];

class  Year / Order=internal descending Style=[font_size=0.5 background=white];

classlev Country Year / Style=[font_size=0.5 background=white];

var wt / Style=[font_size=0.5 background=white];

Table Country = ' ' * Year = ' ',  wt = 'Sales' * f=fmt.  / Condense NoContinued ROW = FLOAT box=[style=[background=white]] style=[frame=void rules=rows background=white];

Run;

ODS PDF CLOSE;

ODS listing;

Xia Keshan

Contributor
Posts: 30

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

Hi Xia,

    Thanks a lot for your help. Now I am using proc report instead of proc tabulate. With your method, it does not work.

    There are two issues:

     Proc report ........

     .......

     define wt / sum format=fmt.;

     .......;

     run;

     It does not show the formatting I want, instead it just displays

     '~S={background=red}-' instead.

     How to solve this?

     By the way, also want sales format to be 6.2, how to control this?

     Thanks a lot for your help.

Jian

Contributor
Posts: 30

Re: Issues with using proc tabulate and ods pdf to output data table into pdf file ...

Hi Xia,

    It works. I forgot to define escapechar. Thanks.

Jian

Ask a Question
Discussion stats
  • 10 replies
  • 500 views
  • 0 likes
  • 4 in conversation