Date:         Sat, 17 Mar 2012 14:02:42 -0400
Reply-To:     Nat Wooding <nathani@VERIZON.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Nat Wooding <nathani@VERIZON.NET>
Subject:      Re: read XLSX files?
In-Reply-To:  <CAO3qWJOypjyaAGabMEjj5wcssv5SN7idT=M=rbPRGAWsdYHSaw@mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-7"

 Sterling

One thing to beware of when importing Excel files into SAS is to be certain
that the files have a consistent layout and for the numeric variables that
there are no variables with missing values that might be read in as
character data and thereby cause problems if you want to concatenate the new
data with existing SAS files.

Nat Wooding

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Sterling Paramore
Sent: Saturday, March 17, 2012 1:46 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: read XLSX files?

I'm asking because I want to be able to automate excel file imports for
daily data loads.  From what you're saying, it sounds like I would need to
have my personal PC or a "server" PC turned on in order for this to work.

On Thu, Mar 15, 2012 at 5:43 PM, DUELL, BOB <bd9439@att.com> wrote:
> If you license "SAS Access to PC File Formats" on UNIX, I think you also
get a copy of the "PC Files Server" for the desktop; I'm not sure.  But if
you have Windows SAS as well, you may already have it.
>
> You do not need the PC Files Server to create Excel files on UNIX; you
only need the "Access to PC File Formats" product.  For example:
>
> proc export data=a
>   file='$HOME/test/excel_test.xls'
>   dbms=xls
>   replace;
>   sheet='My Data A';
> run;
> proc export data=b
>   file='$HOME/test/excel_test.xls'
>   dbms=xls
>   replace;
>   sheet='My Data B';
> run;
>
> However, you may find the "PC Files Server" useful anyway.
>
> The "PC Files Server" is really a Windows "service" installed a PC (your
desktop or perhaps a true Windows server).  You install in on your PC and
configure it to listen to a particular port.  Then it just sits there
waiting a request.  Properly configured, you can send workbooks from UNIX
directly to your PC.  I've only done this once myself as an experiment, but
it does work.
>
> Bob
>
> "I am an AT&T employee and the views expressed are my own."
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@listserv.uga.edu] On Behalf Of
> Sterling Paramore
> Sent: Thursday, March 15, 2012 3:57 PM
> To: SAS-L@listserv.uga.edu
> Subject: Re: read XLSX files?
>
> I've been considering asked our IT department to purchase SAS/ACCESS
> for PC files.  We have SAS running on a UNIX server.  In that case,
> would we need a separate windows box to act as a PC Files server?
>
> On Thu, Mar 15, 2012 at 3:16 PM, Joe Matise <snoopy369@gmail.com> wrote:
>> It's not really a server, it's just a service that runs in the
>> background on windows.   It's probably installed by default - it was
>> in my 9.3 installation.   I think it's called 'server' because its
>> first use was allowing UNIX to pull in Excel data (via a windows machine
running this).
>>
>> -Joe
>>
>> 2012/3/15 F J Kelley <jkelley@uga.edu>
>>
>>> (sigh)
>>> set up a server to read some Excel files?   I appreciate the info,
>>> but who knows if we are even licensed for this? (and I am waiting
>>> now for some faculty member/staff/student to ask about reading these
>>> files ... sure guys, you just have to set up a server, then ...    )
>>>
>>> I gave up and simply used <4-letter word redacted> to read the
>>> spreadsheet/worksheets.   As I may be one of the last on campus who
>>> can still remember how to write <4-letter word redacted> programs, I
>>> can automate this and get the info the director needs.   I will look
>>> into the server stuff, and I really prefer the output from SAS, but
>>> am leaving for a big Pi Day celebration Savannah is having this
>>> Saturday (actual Pi Day was a work day, and they really are into it,
>>> although I'm unclear how green beer relates to ð), so don't have the
time right now.
>>>
>>> Many thanks to everyone who looked into this, --Joe
>>>
>>> ________________________________________
>>> From: SAS(r) Discussion [SAS-L@LISTSERV.UGA.EDU] on behalf of Joe
>>> Matise [ snoopy369@GMAIL.COM]
>>> Sent: Thursday, March 15, 2012 4:16 PM
>>> To: SAS-L@LISTSERV.UGA.EDU
>>> Subject: Re: [SAS-L] read XLSX files?
>>>
>>> SAS will happily import .xlsx with DBMS=EXCEL if you have ACCESS to
>>> PC FILES and your bitness matches (64 bit SAS on 64 bit
>>> Windows/Office installation).   Though code is probably more
>>> portable if you write it that way (using PC Files Server always)...
>>>
>>> -Joe
>>>
>>> On Thu, Mar 15, 2012 at 3:11 PM, Jordan, Lewis <
>>> Lewis.Jordan@weyerhaeuser.com> wrote:
>>>
>>> > SAS can read .xlsx files.
>>> >
>>> > Or, you can save the data as .xls, or .csv.
>>> >
>>> > *All three of these should import an xlsx file if you have SAS PC
>>> > FILES SERVER... (at least it works for me, although I'm running
>>> > 64-bit sas on 64-bit windows).
>>> >
>>> > PROC IMPORT OUT=yoursasdata
>>> > DATAFILE='C:\Users\jordanle\Desktop\Desktop\book1.xlsx'
>>> > DBMS=excelcs REPLACE;
>>> > SHEET='Sheet1$';
>>> > run;
>>> >
>>> >
>>> > PROC IMPORT OUT=yoursasdata2
>>> > DATAFILE='C:\Users\jordanle\Desktop\Desktop\book1.xlsx'
>>> > DBMS=excelcs REPLACE;
>>> > SHEET='Sheet1$';
>>> > port=8621;
>>> > run;
>>> >
>>> >
>>> > PROC IMPORT OUT=yoursasdata3
>>> > DATAFILE='C:\Users\jordanle\Desktop\Desktop\book1.xlsx'
>>> > DBMS=excelcs REPLACE;
>>> > SHEET='Sheet1$';
>>> > port=8621;
>>> > server="localhost";
>>> > run;
>>> >
>>> > *****************************
>>> > Lewis Jordan
>>> > Weyerhaeuser:
>>> > Southern Timberlands R&D
>>> > Cell (Primary): 662-889-4514
>>> > Office: 662-245-5227
>>> > lewis.jordan@weyerhaeuser.com
>>> > *****************************
>>> >
>>> > -----Original Message-----
>>> > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf
>>> > Of F J Kelley
>>> > Sent: Thursday, March 15, 2012 2:58 PM
>>> > To: SAS-L@LISTSERV.UGA.EDU
>>> > Subject: read XLSX files?
>>> >
>>> > I have been sent some data for analysis; it is an Excel file,
>>> > created
>>> with
>>> > Office 2010.   The file name has blanks in it as do the individual
>>> > worksheets.
>>> >
>>> > I'm using SAS 9.3 TS1M0   (32-bit) on Windows 7 (64-bit)
>>> >
>>> > Using File > Import failed
>>> >
>>> > and coding
>>> >
>>> >   libname workbook EXCEL path="D:\Support\SAS\Data\Reports\2011
>>> > Systems Reports.xlsx";
>>> >
>>> >
>>> > ERROR: Connect: Class not registered
>>> > ERROR: Error in the LIBNAME statement.
>>> >
>>> > (same error as with Import).   Not sure if it is the blanks or the
>>> > XSLX or what, and "class not registered"???
>>> >
>>> > so I am kinda wondering if SAS even can read XSLX files.   which
>>> > seems absurd as these have been around for ~5 years now.
>>> >
>>> > The documentation I can find seems to be a mix of things from the
>>> > last century as well as more recent stuff and it is unclear what is
relevant.
>>> >   I'll try ODBC next but thought this was the most straightforward.
>>> >
>>> > Are others able to read XLSX files with SAS/Access for PC File
Formats?
>>> > Thanks,
>>> > --Joe
>>> >
>>>