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

1 REPLY 1
Cynthia_sas
SAS Super FREQ

Hi:

  With TABULATE, you cannot suppress a cell, such as you describe, but with PROC REPORT, you can suppress or alter the data in a cell. Please see this example:

example_with_proc_report.png

 

Produced by this program:


ods msoffice2k file='c:\temp\all_india_mso.html' style=styles.normal;
 

%let finyear=2015;
%let update=9/13/2015;
TITLE1   FONT="Trebuchet MS" height=11pt color=black " Data ItemWise Report for - All India ";
TITLE2   FONT="Trebuchet MS" height=11pt color=black " Provisional Figures for - &Finyear ";
TITLE3   FONT="Trebuchet MS" height=11pt color=black " Status As On: &Update ";


** sort by monthid_n then use order=data in PROC REPORT;
** to make month values sort in desired order;
proc sort data=work.data2;
  by statename_v misconsheadercode_v misconsheadername_v monthid_n;
run;

options missing=' ';
proc report data=WORK.DATA2 nowd missing;
  column STATENAME_V MISCONSHEADERCODE_V MISCONSHEADERNAME_V DETAIL  value_n,MONTHNAME_V value_n=vtot;
  define STATENAME_V / 'State' group
         style(header)=Header{color=vigb FONT_FACE='Trebuchet MS'}
         style(column)=Header{color=vigb FONT_FACE='Trebuchet MS'};
  define misconsheadercode_v / ' 'group 
         style(column)=Header{color=vigb FONT_FACE='Trebuchet MS'};
  define misconsheadername_v / ' 'group 
         style(column)=Header{color=vigb FONT_FACE='Trebuchet MS'};
  define detail / group  ' ' style(column)=Header{color=vigb FONT_FACE='Trebuchet MS'};
  define MONTHNAME_V / across ' ' order=data 
         style(header)=Header{color=vigb FONT_FACE='Trebuchet MS'};
  define value_n / sum ' '
         style(header)=Header{color=vigb FONT_FACE='Trebuchet MS'};
  define vtot / 'Total'
         style(header)=Header{color=vigb FONT_FACE='Trebuchet MS'};
  compute vtot;
    if misconsheadercode_v in ( '1.1.1', '1.3', '1.6.1') then vtot = .;
  endcomp;
run;

ods _all_ close;

 

 

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
  • 1 reply
  • 932 views
  • 0 likes
  • 2 in conversation