BookmarkSubscribeRSS Feed
Syrph
Calcite | Level 5

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

4 REPLIES 4
art297
Opal | Level 21

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!

FriedEgg
SAS Employee

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...

Syrph
Calcite | Level 5

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.

FriedEgg
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1124 views
  • 6 likes
  • 3 in conversation