BookmarkSubscribeRSS Feed
ameey
Calcite | Level 5

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

2 REPLIES 2
ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ

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

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
  • 1045 views
  • 0 likes
  • 3 in conversation