BookmarkSubscribeRSS Feed
ameey
Calcite | Level 5

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;

2 REPLIES 2
ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1119 views
  • 0 likes
  • 3 in conversation