Proc tabulate issue

[ameey]

Hi Cynthia/ All

This is same report, Here is sub header B where there value is coming in a right alignment because in label it is numeric character and using proc format  I have changed the value into a printable character value. Now this character value is still coming in right alignment now I have to put in left alignment which is not going. I have tried to solve it but unable to do it. Please help me out how can i do it. Herewith i am sharing a data set with sas code, print screen of output and desired output.

[Cynthia_sas · Accepted solution]

Hi:

  It was very good of you to post the code you're using, but without understanding the structure of your data or having a sample of your data, it is impossible to comment on the code or run it. Generally speaking, however, your code is very hard to read and without data, cannot be tested. Anyone who makes up some fake data would spend so much time trying to "reverse engineer" some test data that they would probably spend all their cycles on making the test data, when it would be fairly easy for you to append the test data.

  In addition, you use macro variables and user-defined formats -- and you only provide one of them (ABC.), so someone has to guess at those, too. And, it seems to me that you are causing some of your own problems. Why are you making variables in your data that only look like they are holding text strings? For example, in this code:

data WORK.APPEND_TABLE_All2;

  set WORK.APPEND_TABLE_All1;

  /*Need="Need Assessed";*/

  Need=" ";

  Achievement="Achievement during April to &Month_Name ";

  District="State: &State";

run;

  TABULATE will want to put the VALUE for the NEED variable in the cell that you are trying to get rid of. You assigned a value of blank (" ") to that variable. So you are creating your own empty cell. I am not sure why any of these "fake headers" need to be created. TABULATE will not merge headers the way you seem to want. However, you could easily get rid of the "fake" NEED variable in your TABLE statement to eliminate the empty header cell. Or, if you want that cell to say "Need Assessed", then assign that value to the NEED variable. But I'm not sure why you do that, you could simply have something like:

       
Sum ={LABEL='Need Assessed'}*(NeedAssessed ={LABEL="Nos. (A)" } code ={LABEL="Units" } )
or

Sum ={LABEL=' '}*(NeedAssessed ={LABEL="Nos. (A)" } code ={LABEL="Units" } )

...instead of what you're doing now with your "fake" NEED variable. TABULATE will use the SUM statistic for both of your items in parentheses if you put it first instead of attached to each variable. Then this gives you the opportunity to assign a header string of your choosing, such as shown above.

cynthia

[ameey]

Hi Cynthia

Thank you so much and lots of regards u always help us and devote your precious time to resolve our queries.

Regards..

[ameey]

Dear Cynthia/ All Expert,

I took the help in same report earlier also and that was the excellent solution you shared to me . Again there is one change using proc tabulate in same report. I have to put the header A, B C , D , E with formula , F with formula in a separate row. To better understanding  I am sharing the data set with SAS code as well desired excel output sheet to compare with generate excel report. Also I sent you print screens of source report ( which i created previous ) and target report ( changes according to this ). This is request please do needful help even I am trying to resolve it but i am not getting a perfect solution.

I am trying to attched dataset and code also but this is not taking attachment that what i am taking a help on previous comment and here already my dataset and code is available .

programe

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

data Work.APPEND_TABLE3;

set Amit.APPEND_TABLE3;

run;

proc format;

value abc  1='Eligible Couples'

              2='Estd. Live Births'

              3='children aged less than 10 years of age'

              4='children aged less than 16 years of age'

              5='children of 3 year of age'

              6='children of 5 years of age'

              7='children of 9 years of age'

              8='children upto 1 year of age'

              9='pregnant women'

              10=' '

;

ODS tagsets.MSOFFICE2k_x FILE="C:\amit\code\RCH Indicatorwise\All_India.xls"

STYLE=NORMAL headtext="<style> h1 {margin:0}</style>" ;

/*x md "C:\amit\code\RCH Indicatorwise\H. RCH Reports - State Wise\&Financial_Year\MonthUpTo&Month_Name";*/

/**/

/*ODS Msoffice2k_x FILE="C:\amit\code\RCH Indicatorwise\H. RCH Reports - State Wise\&Financial_Year\MonthUpTo&Month_Name\All_India.xls" */

/*STYLE=NORMAL headtext="<style> h1 {margin:0}</style>";*/

/*Title*/

TITLE1 j=c font='verdana' color=black  height=4 BOLD justify=left underlin=0 " Performance on RCH Indicators: India ";

Title2 j=l font='Verdana' color=black  height=3 bold underlin=0 "India";

TITLE3 j=l font=Verdana color=black  height=2 bold underlin=0 "For the period: April 2013 to June 2013";

/*TITLE3 j=l font=Verdana color=black  height=2 bold underlin=0 "For the period: April &YEAR1 to &Month_Name &YEAR5";*/

TITLE4 j=l font=Verdana color=black  height=2 bold underlin=0 "As on 20Jul 2012, 11:00 AM";

/*TITLE4 j=l font=Verdana color=black  height=2 bold underlin=0 "As on &dt";*/

/*TITLE6 j=l font=Verdana color=black  height=2 bold underlin=0 "As on &date,&time1";*/

TITLE5 j=r font=Verdana color=black italic height=2 bold underlin=0 "Refreshed on weekly basis";

FOOTNOTE1 font=verdana  height=1 bold j=left "Note:";

FOOTNOTE2 font=verdana  height=1 j=left "Provisional Figures";

data Work.APPEND_TABLE3;

set Work.APPEND_TABLE3;

/*Need="Need Assessed";*/

Need=" ";

Achievement="Achievement during April to &Month_Name ";

India="India";

run;    

PROC TABULATE

DATA=Work.APPEND_TABLE3 STYLE={bordercolor=#000000 FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN};

VAR NeedAssessed code  CurrFinYearAchivement PreFinYearAchivement PercentageChange PercentagechangeNeedAssessed PerFill / STYLE={bordercolor= #000000 FONT_SIZE=.01 background=#fbf6d9 color=vigb} ;

CLASS ReportTypeDecs_V / STYLE=[bordercolor=#000000 FONT_SIZE=.01 background= #fbf6d9 color=vigb] ORDER=UNFORMATTED MISSING;

CLASS ReportType_v / STYLE=[bordercolor= #000000 FONT_SIZE=.01 background= #fbf6d9 color=vigb] ORDER=UNFORMATTED MISSING;

CLASS Achievement/ STYLE=[bordercolor= #000000 FONT_SIZE=.01 background= #fbf6d9 color=vigb] ORDER=UNFORMATTED MISSING;

CLASS NEED/ STYLE=[bordercolor= #000000 FONT_SIZE=.01 background= #fbf6d9 color=vigb] ORDER=UNFORMATTED MISSING;

class India/ STYLE=[bordercolor= #000000 FONT_SIZE=.01 background= #fbf6d9 color=vigb] order=unformatted missing;

CLASS SNO /STYLE=[bordercolor=#000000 FONT_SIZE=.01 color=vigb] ORDER=UNFORMATTED MISSING;

CLASS Item_code / STYLE=[bordercolor= #000000 FONT_SIZE=.01 background=#fbf6d9 color=vigb] ORDER=UNFORMATTED MISSING;

classlev SNO /  STYLE={bordercolor=#000000 FONT_SIZE=.01 color=vigb FONT_FACE='Calibri'};

classlev ReportTypeDecs_V /  STYLE={bordercolor=#000000 FONT_SIZE=.01 color=vigb FONT_FACE='Calibri'};

classlev ReportType_v /  STYLE={bordercolor=#000000 FONT_SIZE=.01 color=vigb FONT_FACE='Calibri'};

classlev  Achievement / style=[background= #fbf6d9 FONT_SIZE=.01 bordercolor=#000000 color=vigb FONT_FACE='Calibri'];

classlev  Need / style=[background= #fbf6d9 FONT_SIZE=.01 bordercolor=#000000 color=vigb FONT_FACE='Calibri'];

classlev  India / style=[background= #fbf6d9 FONT_SIZE=.01 bordercolor=#000000 color=vigb FONT_FACE='Calibri'];

TABLE

/* ROW Statement */  

ReportType_v={LABEL='Indicator Group' STYLE(CLASSLEV)={bordercolor=#000000 background= #fbf6d9 color=vigb FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN cellwidth=116 vjust=top}}*

SNO ={LABEL='S.No. ' STYLE(CLASSLEV)={bordercolor=#000000 color=vigb FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN cellwidth=60}}*

Item_code={LABEL='Item Code (As per HMIS formats)' STYLE(CLASSLEV)={bordercolor=#000000 background= #fbf6d9 color=vigb FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN cellwidth=240 vjust=top}}*

ReportTypeDecs_V ={LABEL='Item ' STYLE(CLASSLEV)={bordercolor=#000000 color=vigb background= #fbf6d9 FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN cellwidth=240}}

,

/* COLUMN Statement */

Sum ={LABEL="Need Assessed (&YEAR1-&YEAR2)" STYLE={FONT_FACE='Calibri' bordercolor=#000000 color=vigb FONT_SIZE=.01 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN

background=#fbf6d9 cellwidth=162 cellheight=10 vjust=C}}*

(

NeedAssessed ={LABEL="Nos.                           (A)     " STYLE={FONT_FACE='Calibri' bordercolor=#000000 color=vigb FONT_SIZE=.01 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN

background=#fbf6d9 cellwidth=100 cellheight=40 Vjust=center }} code ={LABEL="Units                            (B)" STYLE={FONT_FACE='Calibri' bordercolor=#000000 color=vigb FONT_SIZE=.01 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN

background=#fbf6d9 cellwidth=62 cellheight=40 vjust=center}}

)

Achievement={LABEL=" " STYLE={bordercolor=#000000 color=vigb background= #fbf6d9 FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=BOLD

FONT_STYLE=ROMAN cellwidth=390 cellheight=1 vjust=top }}*

(

India={LABEL=" " STYLE={bordercolor=#000000 color=vigb background= #fbf6d9 FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=BOLD

FONT_STYLE=ROMAN JUST=CENTER vjust=top cellwidth=390 cellheight=1}}*

(

CurrFinYearAchivement ={LABEL="       (&YEAR1-&YEAR2)            (C)                   " STYLE={bordercolor=#000000 color=vigb background= #fbf6d9 FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN

cellwidth=91    Vjust=center}}* Sum ={LABEL=''}*f=TonsNum.* {STYLE={FONT_FACE='Calibri' FONT_SIZE=.01

FONT_STYLE=ROMAN FONT_WEIGHT=MEDIUM JUST=right bordercolor=#000000}}

PreFinYearAchivement ={LABEL="        (&YEAR3-&YEAR4)             (D)                  " STYLE={bordercolor=#000000 background= #fbf6d9 color=vigb FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN

cellwidth=91  Vjust=center just=center}}* Sum ={LABEL=''}*f=TonsNum.* {STYLE={FONT_FACE='Calibri' FONT_SIZE=.01

FONT_STYLE=ROMAN FONT_WEIGHT=MEDIUM bordercolor=#000000}}

PercentageChange  ={LABEL='% Change                                                                                (E =((C-D)/D)*100)' STYLE={bordercolor=#000000 background= #fbf6d9 color=vigb FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN

cellwidth=91 Vjust=center}}* Sum ={LABEL=''}*f=12.1* {STYLE={HTMLSTYLE="VND.MS-EXCEL.NUMBERFORMAT:#0.0"

FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN JUST=right bordercolor=#000000}}) ) 

PercentagechangeNeedAssessed  ={LABEL="% Achievement of need assessed     (&YEAR1-&YEAR2)                                                                      

(F =(C/A)*100)" STYLE={bordercolor=#000000 background= #fbf6d9 color=vigb FONT_FACE='Calibri' FONT_SIZE=.01 FONT_WEIGHT=BOLD

FONT_STYLE=ROMAN cellwidth=134 cellheight= 50 vjust= C}}* Sum ={LABEL=''}*f=12.1* 

{STYLE={HTMLSTYLE="VND.MS-EXCEL.NUMBERFORMAT:#0.0" FONT_FACE='Calibri' FONT_SIZE=.01

FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN JUST=right bordercolor=#000000}}        

/ BOX={LABEL="Indicators" STYLE={bordercolor=#000000 background= #fbf6d9 color=vigb FONT_FACE='Calibri' FONT_SIZE= .4

FONT_WEIGHT=BOLD FONT_STYLE=ROMAN VJUST=center cellpadding=0}} style

=[bordercolor=black BorderWidth=1 ]  MISSTEXT=' ' ;

RUN;

TITLE; FOOTNOTE;

run;quit;

ODS _ALL_ CLOSE;

Regards,

Regards,

+918010732125