Date:         Mon, 19 Oct 2009 14:11:43 -0700
Reply-To:     mlhoward@avalon.net
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Proc Import Excel 2007 with SAS 9.2
Comments: To: "Hoskins, Richard E. (DOH)" <Richard.Hoskins@DOH.WA.GOV>
Content-Type: text/plain; charset="UTF-8"

Richard,

I think using the tagset is the best you can do; Proc Export has never
been very good at holding the formats of variables and I'm not sure
if 9.2 can handle more than 256 columns.   Ask if you need help
with setting a style.  These will be a little slow with reading in
.xml so you want to save as .xlxs once you get it into Excel 2007 before
you send to your clients.

Also the most recent version of the Excel Tagset is on the SAS site.

-Mary

--- Richard.Hoskins@DOH.WA.GOV wrote:

From: "Hoskins, Richard E.  (DOH)" <Richard.Hoskins@DOH.WA.GOV>
To: <mlhoward@avalon.net>
Subject: RE: Proc Import Excel 2007 with SAS 9.2
Date: Mon, 19 Oct 2009 13:50:48 -0700

Thanks a lot - not what I was asking in this note, but something I
definitely needed. I just tried the tagset info you sent. Not quite
there but I am getting it.

I meant export not import. Sorry for the confusion.

I wanted to be able to export to Excel 2007 because my dataset has 400
columns and often more than
64000 rows. I definitely do not do anything serious in my analyses with
data stored this way but sometimes I have to distribute data this way
because of limitations of my "customers".

I can't find anything that says SAS 9.2 will export to Excel 2007.

Thanks again for your reply. Definitely useful.



________________________
Forward or reply from:
Dick Hoskins
EMS/Trauma
WA DOH, Olympia
(360) 236-2873
richard.hoskins@doh.wa.gov
_________________________


-----Original Message-----
From: Mary [mailto:mlhoward@avalon.net]
Sent: Monday, October 19, 2009 12:41 PM
To: Hoskins, Richard E. (DOH)
Cc: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Proc Import Excel 2007 with SAS 9.2

Richard,

You are saying Proc Import on the subject line but asking how to export?
I would recommend Excel Tagsets; since it will preserve your formatting
(you can create your own styles if you wish for fonts and font size):

ods listing close;
ods tagsets.excelxp file='c:\temp\out.xml'
style=minimal

options(default_column_width='20,20,10,10,10,10,10,10,10,10,10,10,10,10,
10'
sheet_label=' ' autofit_height="yes"
ORIENTATION = 'landscape'
center_horizontal = 'yes'
center_vertical = 'yes'
PRINT_FOOTER = "run 10/2/2009");
ods tagsets.excelxp options(sheet_name="Sheet1"
default_column_width='20,20,10,10,10,10,10,10,10,10,10,10,10,10,10');

proc report...

ods tagsets.excelxp options(sheet_name="Sheet2"
default_column_width='20,20,10,10,10,10,10,10,10,10,10,10,10,10,10');

proc report...

ods tagsets.excelxp close;
ods listing;

-Mary


--- Richard.Hoskins@DOH.WA.GOV wrote:

From: "Hoskins, Richard E. (DOH)" <Richard.Hoskins@DOH.WA.GOV>
To: SAS-L@LISTSERV.UGA.EDU
Subject:      Proc Import Excel 2007 with SAS 9.2
Date:         Mon, 19 Oct 2009 11:24:05 -0700

How can one export a SAS file to Excel 2007? Essentially unlimited
columns and rows unlike previous Excel versions.

R Hoskins
WA State DOH