proc tabulate issue

ameey — Fri, 27 Mar 2015 02:51:31 GMT

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.

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,

+918010732125

India

Re: proc tabulate issue

ballardw — Fri, 27 Mar 2015 15:35:49 GMT

To clarify: You are asking to have an excel formula with references to some cells, that may not be in the table created by proc tabulate?

And you want the cell to resolve to a value when viewed in Excel?

And this MUST go into a cell that contains text from a variable label or name?

As an aside, for clarity and focus on your manipulation question style appearance elements only hide what the code is doing.

Re: proc tabulate issue

Cynthia_sas — Fri, 27 Mar 2015 15:45:58 GMT

Hi:

If I understand what you want, it cannot be done with TAGSETS.MSOFFICE2K_X. If you want an EXCEL formula in your output report, then that is not possible with TAGSETS.MSOFFICE2K_X. Remember that even when you use a file extension of .XLS, you are NOT creating a true, binary XLS file. You are creating a basic HTML file that Excel knows how to open and render. If you look at the internal page for MSOFFICE2K_X Base SAS: The MSOffice2K_x Tagset Adds Options to the MSOffice2K Tagset you will see there is no way to specify a formula in the cells. And certainly, there is no way to insert an extra "row" in TABULATE output.

The TAGSETS.EXCELXP destination does allow you to insert EXCEL formulas into your (XML - Spreadsheet Markup Language 2003) output report. See the example here: Base SAS: Demo: ExcelXP Tagset and Microsoft Excel BUT, again, TABULATE will NOT insert extra rows. I did not see any example of your DESIRED output in the zip files. I only saw code and data.

cynthia

topic Re: proc tabulate issue in ODS and Base Reporting

proc tabulate issue

Re: proc tabulate issue

Re: proc tabulate issue