The SAS Output Delivery System and reporting techniques

ods and proc tabulate report

Reply
Occasional Contributor
Posts: 19

ods and proc tabulate report

Hi Cynthia/All Please guide me how can I keep the value of total column for a particular indicator as a blank cell in proc tabulate. As given below i have enclosed a the requirement sheet where we have to put blank cell in a total column for a particular indicators only and rest should come  with the sum of all months.  I have attached a small Data set and sas code. Anyone can reply me with correct solution.

print screen.png

LIBNAME Amit 'C:\amit\code\RCH Indicatorwise';

run;

data amit.DATA2 ;

set Amit.DATA1( firstobs= 1 obs= 10000);

run;

data WORK.data2;

set Amit.data2;

run;

ods Tagsets.Msoffice2k_x file="C:\amit\code\RCH Indicatorwise\C. Data ItemWise (Across States, Districts, Months and Blocks)\5. All States and Districts Across Months\&Finyear\All_India.xls"

   options(

            data_type="String,String,String,String,String,String,Number,Number,Number,Number,Number,Number,Number,Number,Number,Number,Number,Number,Number"

            column_width="2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3"

                  orientation="landscape"

                  pageheight="95%"

                  pagewidth="100%"

                  margin=".1in .1in .1in .3in"

                  print_header_margin=".1in"

                  print_footer_margin=".1in"

                  fit2page="yes"

                  excel_scale="95"

                  Auto_format="list"

            ) style=styles.Normal  headtext="<style> h1 {margin:0}</style>"

;

/*ods Tagsets.Msoffice2k_x file="&path.SASReports\C. Data ItemWise (Across States, Districts, Months and Blocks)\5. All States and Districts Across Months\&Finyear\All_India.xls" */

/*   options(*/

/*            data_type="String,String,String,String,String,String,Number,Number,Number,Number,Number,Number,Number,Number,Number,Number,Number,Number,Number"*/

/*            column_width="2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3"*/

/*                  orientation="landscape"*/

/*                  pageheight="95%"*/

/*                  pagewidth="100%"*/

/*                  margin=".1in .1in .1in .3in"*/

/*                  print_header_margin=".1in"*/

/*                  print_footer_margin=".1in"*/

/*                  fit2page="yes"*/

/*                  excel_scale="95"*/

/*                  Auto_format="list"*/

/*            ) style=styles.Normal  headtext="<style> h1 {margin:0}</style>"*/

/**/

/*;*/

/*ODS MSOffice2K file="C:\SASReports\I.Data ItemWise\&State\&State..xls" STYLE=normal; */

TITLE1   FONT="Trebuchet MS" height=3 color=black " Data ItemWise Report for - All India ";

TITLE2   FONT="Trebuchet MS" height=3 color=black " Provisional Figures for - &Finyear ";

/*TITLE3   FONT="verdana" height=4 color=black " Status As On: %SYSFUNC(DATE(), EURDFDE9.) at %SYSFUNC(TIME(), TIMEAMPM8.) ";*/

TITLE3   FONT="Trebuchet MS" height=3 color=black " Status As On: &Update ";

/* -------------------------------------------------------------------

   Run the tabulate procedure

   ------------------------------------------------------------------- */

/**/

/*data WORK.Data2;*/

/*set work.data2;*/

/*total= '0';*/

/*run;*/

PROC TABULATE DATA=WORK.DATA2 STYLE={bordercolor=vigb};

      VAR VALUE_N / STYLE={bordercolor=vigb} ;

      CLASS STATENAME_V / ORDER=DATA MISSING;

/*      CLASS DISTRICTNAME_V / ORDER=DATA MISSING;*/

      CLASS MISCONSHEADERCODE_V /ORDER=DATA MISSING;

      CLASS MISCONSHEADERNAME_V /ORDER=DATA MISSING;

      CLASS MONTHNAME_V / ORDER =DATA MISSING;

      CLASS DETAIL/ ascending  MISSING;    

      CLASSLEV STATENAME_V / STYLE={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' VJUST=TOP};

      CLASSLEV MONTHNAME_V / STYLE={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' VJUST=TOP };

      CLASSLEV MISCONSHEADERCODE_V / STYLE={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' VJUST=TOP};

      CLASSLEV MISCONSHEADERNAME_V / STYLE={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' VJUST=TOP};

      CLASSLEV DETAIL / STYLE={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' VJUST=TOP};

      TABLE

/* Row Dimension */

(STATENAME_V ={Label= 'State' style={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' FONT_SIZE=2 FONT_WEIGHT=bold just=Center VJUST=TOP cellwidth=20}}*

/*DISTRICTNAME_V ={Label= 'District' style={FONT_FACE='dsr' FONT_SIZE=2 FONT_WEIGHT=bold just=Center VJUST=TOP cellwidth=20}}**/

/*     CODEWITHSECTION ={Label= ' ' style={FONT_FACE='dsr' FONT_SIZE=2 FONT_WEIGHT=bold just=Center VJUST=TOP cellwidth=20}}**/

      MISCONSHEADERCODE_V ={Label= ' ' style={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' FONT_SIZE=2 FONT_WEIGHT=bold just=Center VJUST=TOP cellwidth=22}}*

        MISCONSHEADERNAME_V ={Label= ' ' style={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' FONT_SIZE=2 FONT_WEIGHT=bold just=Center VJUST=TOP cellwidth=130}}*

                  DETAIL ={Label= ' ' style={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' FONT_SIZE=2 FONT_WEIGHT=bold just=Center VJUST=TOP cellwidth=28}}),

/* Column Dimension */

/*        (MONTHNAME_V=' ' total={Label=' ' style={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' FONT_SIZE=2 FONT_WEIGHT=bold just=Center VJUST=TOP cellwidth=58}})**/

/*                        VALUE_N   =' '*Sum='' *f=PRN. / MISSTEXT=' '  box={label=' ' style={bordercolor=vigb}};*/

                                   

                  (MONTHNAME_V=' ' All={Label='Total' style={bordercolor=vigb color=vigb FONT_FACE='Trebuchet MS' FONT_SIZE=2 FONT_WEIGHT=bold just=Center VJUST=TOP cellwidth=58}})*

                        VALUE_N   =' '*Sum='' *f=PRN. / MISSTEXT=' '  box={label=' ' style={bordercolor=vigb}};

      ;

RUN;

ods _all_ close;

Attachment
Super User
Posts: 11,121

Re: ods and proc tabulate report

With some requirement to provide blank cells for only some rows I would summarize the data, in this case generate an output data set from proc tablulate. Use a data step to set those specific values to missing and then output that dataset with settings or formats to have the missing values as blank.

SAS Super FREQ
Posts: 8,819

Re: ods and proc tabulate report

With TABULATE, you cannot just "blank" out certain cells. This is something you would be more likely to do with PROC REPORT. Or, as ballardw suggested, pre-summarize the data and then blank out the total that you don't want.

cynthia

Ask a Question
Discussion stats
  • 2 replies
  • 502 views
  • 0 likes
  • 3 in conversation