topic Re: Suppress_bylines = 'no' with Proc Print in Microsoft Integration with SAS

Suppress_bylines = 'no' with Proc Print

deleted_user — Wed, 15 Jul 2009 22:39:55 GMT

I want to output the by goups in the following to the same sheet (in EXCEL) using the EXCELXP tagset. I want to display the byline on the row about each new by group (like Year = 2006 Procedure_sort = A).

The following code works if I use the suppress_bylines = "yes" and in that case the byline is blank.

Here is code

%let _ODSDEST=tagsets.ExcelXP;
%let _ODSSTYLE=Normal;

ods &_ODSDEST path='c:\temp' file='ExcelXP-sample10.xml' style=&_ODSSTYLE
options( skip_space='3,2,0,0,1' sheet_interval='none' embedded_titles='yes'
suppress_bylines='no' );

PROC PRINT DATA=TEMP3 NOBS SPLIT="*";
VAR LOCATION_SORT ALLPLAN_ALLOWED_AMT SELF_INSURED_ALLOWED_AMT NON_GROUP_ALLOWED_AMT
SMALL_GROUP_ALLOWED_AMT MEDIUM_GROUP_ALLOWED_AMT LARGE_GROUP_ALLOWED_AMT;
BY YEAR PROVIDER_SORT;
LABEL LOCATION_SORT = "LOCATION*OF SERVICE"
ALLPLAN_ALLOWED_AMT = "ALL*PLANS"
SELF_INSURED_ALLOWED_AMT ="SELF-INSURED*PLANS"
NON_GROUP_ALLOWED_AMT = "NON-GROUP*PLANS"
SMALL_GROUP_ALLOWED_AMT = "SMALL*GROUP*PLANS"
MEDIUM_GROUP_ALLOWED_AMT = "MEDIUM*GROUP*PLANS"
LARGE_GROUP_ALLOWED_AMT = "LARGE*GROUP*PLANS";
RUN;

Any help would be appreciated.

Thanks

Tom

Re: Suppress_bylines = 'no' with Proc Print

deleted_user — Wed, 15 Jul 2009 22:44:16 GMT

I should have added -- when I say not working -- I mean it creates the output file but it cannot be opened in EXCEL (using EXCEL version 2007)

Re: Suppress_bylines = 'no' with Proc Print

Cynthia_sas — Thu, 16 Jul 2009 02:48:37 GMT

Hi:
I figure, from your use of &_ODSDEST and &_ODSSTYLE that you have code that creates a stored process using the SAS Enterprise Intelligence Platform.

The only kind of results that Excel can "receive" from a stored process are: CSV, HTML and SASReport XML, as explained in this Tech Support Note.
http://support.sas.com/kb/36/031.html

There are some other ODS destinations that you can use in stored processes, but they are mostly HTML based, such as PHTML, CHTML or MSOFFICE2K.

Normally, you cannot use TAGSETS.EXCELXP as an override to _ODSDEST -- excpet in the Information Deliver Portal. The type of XML created by TAGSETS.EXCELXP is not compatible with the Enterprise Intelligence Platform. In SAS 9.1.3, I did test TAGSETS.EXCELXP with the SAS Enterprise Intelligence Platform and the only client applications where you can use TAGSETS.EXCELXP are the Information Delivery Portal and/or SAS Enterprise Guide. (And, if you use TAGSETS.EXCELXP with SAS Enterprise Guide, you cannot open the Spreadsheet Markup Language XML file inside of EG in EG 4.1.)

I suggest you either search previous forum postings for more information about how to make this work in the Information Deliver Portal or work with Tech Support on this issue.

Cynthia