ODS Tagsets ExcelXP [SAS]

From: Jen on 9 May 2006 09:46

Hello Everyone,

I am using ODS Tagsets ExcelXP to export several datasets into Excel
mutiple worksheets. I specified the worksheets' name using the
following code. However .xls file I gets does not show me the
worksheets name according to what I specified

Another question is that I cannot do further calculation on the
negative values I export from my SAS datasets into Excel using ODS
Tagsets ExcelXP.

For example ( 1.74%) should be -1.74%, but excel shows (1.74%).
$-3,027,863 should be -3027863, excel shows $-3027863. Excel does not
allow me to do any other calculation based on the this result. So, I am
wondering if my code is correct or there is any other way to fix that.

I am not very familiar with ODS Tagsets ExcelXP. I saw that it has the
function to import and export datasets into or from multiple Excel
worksheets. I am wondering if it is better than SAS/ACCESS? Currently,
we do not have SAS/ACCESS, so we cannot use proc import or proc export
to load excel file using SAS.

Thank you very much!

ODS TAGSETS.EXCELXP OPTIONS(EMBEDDED_TITLES='YES' SHEET_NAME =
'Collision');
PROC PRINT DATA = US_COLL NOOBS LABEL;
VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov
YTD_Recov_Tar Recov_Plan PCTN_Recov&m PCTN_Recov_Tar
YTD_PCTN_Recov PCTN_Recov_plan;
TITLE1 "COLLISION RECOVERY REPORT";

RUN;
ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME = 'Comprehensive');
PROC PRINT DATA = US_COMP NOOBS LABEL;
VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov
PCTN_Recov&m YTD_PCTN_Recov;
TITLE1 "COMPREHENSIVE SUBROGATION RECOVERY REPORT";
RUN;
ODS TAGSETS.EXCELXP CLOSE;

From: Peter Crawford on 9 May 2006 11:03

On Tue, 9 May 2006 06:46:50 -0700, Jen <leiliang77(a)GMAIL.COM> wrote:

>Hello Everyone,
>
>I am using ODS Tagsets ExcelXP to export several datasets into Excel
>mutiple worksheets. I specified the worksheets' name using the
>following code. However .xls file I gets does not show me the
>worksheets name according to what I specified
>
>Another question is that I cannot do further calculation on the
>negative values I export from my SAS datasets into Excel using ODS
>Tagsets ExcelXP.
>
>For example ( 1.74%) should be -1.74%, but excel shows (1.74%).
> $-3,027,863 should be -3027863, excel shows $-3027863. Excel does not
>allow me to do any other calculation based on the this result. So, I am
>wondering if my code is correct or there is any other way to fix that.
>
>I am not very familiar with ODS Tagsets ExcelXP. I saw that it has the
>function to import and export datasets into or from multiple Excel
>worksheets. I am wondering if it is better than SAS/ACCESS? Currently,
>we do not have SAS/ACCESS, so we cannot use proc import or proc export
>to load excel file using SAS.
>
>Thank you very much!
>
>
>ODS TAGSETS.EXCELXP OPTIONS(EMBEDDED_TITLES='YES' SHEET_NAME =
>'Collision');
> PROC PRINT DATA = US_COLL NOOBS LABEL;
> VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m
YTD_Recov
> YTD_Recov_Tar Recov_Plan PCTN_Recov&m
PCTN_Recov_Tar
> YTD_PCTN_Recov PCTN_Recov_plan;
> TITLE1 "COLLISION RECOVERY REPORT";
>
> RUN;
> ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME = 'Comprehensive');
> PROC PRINT DATA = US_COMP NOOBS LABEL;
> VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m
YTD_Recov
> PCTN_Recov&m YTD_PCTN_Recov;
> TITLE1 "COMPREHENSIVE SUBROGATION RECOVERY
REPORT";
> RUN;
>ODS TAGSETS.EXCELXP CLOSE;

sounds like an excel import problem.

From: Jack Hamilton on 4 May 2006 21:33

On 5/9/2006 6:46 AM Jen said the following:
> Hello Everyone,
>
> I am using ODS Tagsets ExcelXP to export several datasets into Excel
> mutiple worksheets. I specified the worksheets' name using the
> following code. However .xls file I gets does not show me the
> worksheets name according to what I specified

What names are you getting?

Are you using the latest version of the tagset? I know there have
recently been changes in this area. The latest version prints this
message in the log:

NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.34, 04/07/06). Add
options(doc='help') to the ods statement for more information.

(The web page, <http://support.sas.com/rnd/base/topics/odsmarkup/, says
that the tagset was updated in May; I on't know why the info line says
April.)

> Another question is that I cannot do further calculation on the
> negative values I export from my SAS datasets into Excel using ODS
> Tagsets ExcelXP.
>
> For example ( 1.74%) should be -1.74%, but excel shows (1.74%).
> $-3,027,863 should be -3027863, excel shows $-3027863. Excel does not
> allow me to do any other calculation based on the this result. So, I am
> wondering if my code is correct or there is any other way to fix that.

It's probably interpreting the numbers as strings, but it's hard to say
why that might be. I use ExcelXP a lot in production programs, and I
don't have this problem. You might play around with the formats of the
variables in question. Also, there's a whole slew of tagset options
dealing with how numbers are interpreted. Use the DOC='HELP' option to
get a list.

> I am not very familiar with ODS Tagsets ExcelXP. I saw that it has the
> function to import and export datasets into or from multiple Excel
> worksheets.

No, it doesn't import or export to Excel. It creates an XML file (not a
native format file) that Excel knows how to read. If you want to import
data from an existing Excel workbook, the ExcelXP tagset won't help you.

>I am wondering if it is better than SAS/ACCESS? Currently,
> we do not have SAS/ACCESS, so we cannot use proc import or proc export
> to load excel file using SAS.

If you don't have SAS/Access, then the tagset is definitely a better
choice.

> Thank you very much!
>
>
> ODS TAGSETS.EXCELXP OPTIONS(EMBEDDED_TITLES='YES' SHEET_NAME =
> 'Collision');
> PROC PRINT DATA = US_COLL NOOBS LABEL;
> VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov
> YTD_Recov_Tar Recov_Plan PCTN_Recov&m PCTN_Recov_Tar
> YTD_PCTN_Recov PCTN_Recov_plan;
> TITLE1 "COLLISION RECOVERY REPORT";
>
> RUN;
> ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME = 'Comprehensive');
> PROC PRINT DATA = US_COMP NOOBS LABEL;
> VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov
> PCTN_Recov&m YTD_PCTN_Recov;
> TITLE1 "COMPREHENSIVE SUBROGATION RECOVERY REPORT";
> RUN;
> ODS TAGSETS.EXCELXP CLOSE;
>

From: yzg9 on 10 May 2006 11:07

Ah, I see a new version, May 10, 2006 is the date stamp on the file to
download.

Interesting issue though...

I just ran this code:
ODS tagsets.excelxp
file="Y:\SAS Programs\Tagsets\ExcelXPHelpMay2006.xml"
options(doc="help") ;
ods tagsets.excelxp close;

And the resulting file, "ExcelXPHelpMay2006.xml", cannot be read by
Excel (error window pops up and says: "Unable to read file").

When I am able to get it open (via MSWord, showing the XML tags) there
isn't any text besides the header information (unless the font color is
white), just lots of border tags and paragraph symbols (placed there by
Word) - no text.

There is text in the LOG.

John Gerstle, MS
Biostatistician
Northrop Grumman
CDC Information Technological Support Contract (CITS)
NCHSTP \DHAP \HICSB \Research, Analysis, and Evaluation Section
Centers for Disease Control and Prevention

"As far as the laws of mathematics refer to reality, they are not
certain; and as far as they are certain, they do not refer to reality."

Albert Einstein, addressing the Prussian Acadamy of
Science, Berlin , Jan 27, 1921

"Boss. We've got cats." "Meow"

>> -----Original Message-----
>> From: owner-sas-l(a)listserv.uga.edu
[mailto:owner-sas-l(a)listserv.uga.edu]
>> On Behalf Of Jack Hamilton
>> Sent: Thursday, May 04, 2006 9:33 PM
>> To: Jen
>> Cc: SAS-L(a)LISTSERV.UGA.EDU
>> Subject: Re: [SAS-L] ODS Tagsets ExcelXP
>>
>> On 5/9/2006 6:46 AM Jen said the following:
>> > Hello Everyone,
>> >
>> > I am using ODS Tagsets ExcelXP to export several datasets into
Excel
>> > mutiple worksheets. I specified the worksheets' name using the
>> > following code. However .xls file I gets does not show me the
>> > worksheets name according to what I specified
>>
>> What names are you getting?
>>
>> Are you using the latest version of the tagset? I know there have
>> recently been changes in this area. The latest version prints this
>> message in the log:
>>
>> NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.34, 04/07/06).
Add
>> options(doc='help') to the ods statement for more information.
>>
>> (The web page, <http://support.sas.com/rnd/base/topics/odsmarkup/,
says
>> that the tagset was updated in May; I on't know why the info line
says
>> April.)
>>
>> > Another question is that I cannot do further calculation on the
>> > negative values I export from my SAS datasets into Excel using ODS
>> > Tagsets ExcelXP.
>> >
>> > For example ( 1.74%) should be -1.74%, but excel shows (1.74%).
>> > $-3,027,863 should be -3027863, excel shows $-3027863. Excel does
not
>> > allow me to do any other calculation based on the this result. So,
I am
>> > wondering if my code is correct or there is any other way to fix
that.
>>
>> It's probably interpreting the numbers as strings, but it's hard to
say
>> why that might be. I use ExcelXP a lot in production programs, and I
>> don't have this problem. You might play around with the formats of
the
>> variables in question. Also, there's a whole slew of tagset options
>> dealing with how numbers are interpreted. Use the DOC='HELP' option
to
>> get a list.
>>
>> > I am not very familiar with ODS Tagsets ExcelXP. I saw that it has
the
>> > function to import and export datasets into or from multiple Excel
>> > worksheets.
>>
>> No, it doesn't import or export to Excel. It creates an XML file
(not a
>> native format file) that Excel knows how to read. If you want to
import
>> data from an existing Excel workbook, the ExcelXP tagset won't help
you.
>>
>> >I am wondering if it is better than SAS/ACCESS? Currently,
>> > we do not have SAS/ACCESS, so we cannot use proc import or proc
export
>> > to load excel file using SAS.
>>
>> If you don't have SAS/Access, then the tagset is definitely a better
>> choice.
>>
>> > Thank you very much!
>> >
>> >
>> > ODS TAGSETS.EXCELXP OPTIONS(EMBEDDED_TITLES='YES' SHEET_NAME =
>> > 'Collision');
>> > PROC PRINT DATA = US_COLL NOOBS LABEL;
>> > VAR Reg State_N Loss1-Loss&m YTD_Loss
Recov1-Recov&m
>> YTD_Recov
>> > YTD_Recov_Tar Recov_Plan PCTN_Recov&m
>> PCTN_Recov_Tar
>> > YTD_PCTN_Recov PCTN_Recov_plan;
>> > TITLE1 "COLLISION RECOVERY REPORT";
>> >
>> > RUN;
>> > ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME = 'Comprehensive');
>> > PROC PRINT DATA = US_COMP NOOBS LABEL;
>> > VAR Reg State_N Loss1-Loss&m YTD_Loss
Recov1-Recov&m
>> YTD_Recov
>> > PCTN_Recov&m YTD_PCTN_Recov;
>> > TITLE1 "COMPREHENSIVE SUBROGATION RECOVERY
>> REPORT";
>> > RUN;
>> > ODS TAGSETS.EXCELXP CLOSE;
>> >

From: Dianne Rhodes on 10 May 2006 11:30

> From: owner-sas-l(a)listserv.uga.edu
> [mailto:owner-sas-l(a)listserv.uga.edu] On Behalf Of Gerstle,
> John (CDC/NCHSTP/DHAP) (CTR)
>
>
> Ah, I see a new version, May 10, 2006 is the date stamp on
> the file to download.
>
> Interesting issue though...
>
> I just ran this code:
> ODS tagsets.excelxp
> file="Y:\SAS Programs\Tagsets\ExcelXPHelpMay2006.xml"
> options(doc="help") ;
> ods tagsets.excelxp close;
>
> And the resulting file, "ExcelXPHelpMay2006.xml", cannot be
> read by Excel (error window pops up and says: "Unable to read file").
>
> When I am able to get it open (via MSWord, showing the XML
> tags) there isn't any text besides the header information
> (unless the font color is white), just lots of border tags
> and paragraph symbols (placed there by
> Word) - no text.
>
> There is text in the LOG.
>
>

What were you expecting to be in the file? The DOC option writes to the
Log. You need a proc print or something that generates output to ODS.
I've been doing this with proc tabulate, and I right click on the file
from Windows and select Open with Excel. Very nice formatted output.

Dianne Rhodes @ westat

|
Pages: 1
Prev: jackknife concept
Next: SYMGET