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;
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.
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.