ods and proc tabulate report

ameey — Thu, 07 May 2015 14:18:10 GMT

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.

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;

Re: ods and proc tabulate report

ballardw — Thu, 07 May 2015 15:28:54 GMT

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.

Re: ods and proc tabulate report

Cynthia_sas — Thu, 07 May 2015 15:40:27 GMT

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

topic Re: ods and proc tabulate report in ODS and Base Reporting

ods and proc tabulate report

Re: ods and proc tabulate report

Re: ods and proc tabulate report