topic Retaining datetime formats from excel files when using Proc import on a 64bit SAS system when Dbms=EXCEL does not work in SAS Procedures

Retaining datetime formats from excel files when using Proc import on a 64bit SAS system when Dbms=EXCEL does not work

Syrph — Tue, 06 Sep 2011 19:31:10 GMT

Hello

I was recently upgraded to a 64 bit version of Windows 7 using SAS 9.2. It appears that I cannot use dbms=Excel to import my excel files anymore for the reason explained more fully in the following post

http://support.sas.com/kb/33/228.html

I do not use a server for which to use EXCELCS, so I am left with using the Dbms=xls. Unfortunatly most of my programs import files with datetime formated variables but when I use the dbms=xls they are not retained. Using the origonal dbms=Excel on my old system I used various options such as USEDATE=NO to allow me to import a full datetime variable. It does not appear that dbms=xls has such features (or at least I haven't been able to find them). Can anyone point me in the right direction to solve this one or recommend any alternatives?

Thanks

Retaining datetime formats from excel files when using Proc import on a 64bit SAS system when Dbms=EXCEL does not work

art297 — Tue, 06 Sep 2011 22:28:15 GMT

I, personally, would like someone from SAS to respond to your question by explaining to all of us how we are supposed to go about installing/using the server mentioned in the documentation for Excelcs.

I'm sure that you aren't the only one stuck in the current situation!

Retaining datetime formats from excel files when using Proc import on a 64bit SAS system when Dbms=EXCEL does not work

FriedEgg — Tue, 06 Sep 2011 22:41:41 GMT

This info would indeed be very nice. I haven't really looked around for it but it would come in handy. Another option to consider is DDE (Dynamic Data Exchange). I have never used this with excel but I know it is possible (not sure whether 64 bit architecture is an issue or not). The last option I can think of is using SAS/ODBC with the excel drivers to connect to an excel workbook another thing I have not done before but should be possible...

Retaining datetime formats from excel files when using Proc import on a 64bit SAS system when Dbms=EXCEL does not work

Syrph — Wed, 07 Sep 2011 18:11:08 GMT

Thanks for those ideas both appear to work but using the SAS/ODBC seems to work the best for me even though it is somewhat annoying to modify my codes to use it. The potential problem for anyone else using this option is that I have installed a 64 bit version of Microsoft office 2010 which installs the ODBC drivers for microsoft excel and access. This does not happen if you have a 32 bit version of Office. I needed to reinstall my office to get this to work.

It would be very nice to get that information about EXCELCS from the people at SAS. I was wondering since SAS seems to create its own standalone server on the computer it is running on, could it be tricked into just using that server? That would be much nicer.

Retaining datetime formats from excel files when using Proc import on a 64bit SAS system when Dbms=EXCEL does not work

FriedEgg — Sun, 11 Sep 2011 04:33:27 GMT

I asked this question in the new SAS Deployment community here is the link:

Paul Homes wrote:

Hi,

In terms of configuration and administration, I would start out by looking in the SAS/ACCESS(R) 9.2 Interface to PC Files: Reference, Second Edition under LIBNAME Statement and Pass-Through Facility on Linux, UNIX, and 64-Bit Microsoft Windows and then PC Files Server Administration.

Cheers

Paul

Resa wrote:
In addition to what Paul Homes indicated:
From experience please take notice of the following Usage Notes:
35064 Network drives are not recognized by the SAS® PC File Server when the server is installed as a service
41060 The EXCELCS and PCFILES engines do not support the GETNAMES= and MIXED= options
The syntax when using EXCELCS is basically as follows:
PROC EXPORT
        DATA = <SAS Dataset>
        OUTFILE = <Excel file>
        DBMS = EXCELCS
        REPLACE ;
    SERVER = <server name> ;
    PORT = <portnumber> ;
    SSPI = YES ;
RUN ;
PROC IMPORT
        OUT = <SAS Dataset>
        DATAFILE = <Excel file>
        DBMS = EXCELCS
        REPLACE ;
    SERVER = <server name> ;
    PORT = <portnumber> ;
    SSPI = YES ;
RUN ;
We use SSPI=YES to allow Integrated Windows Authentication as described on this page.

http://communities.sas.com/message/104758#104758