Help! I'm an advanced and experienced SAS user but this is my first time using SAS Enterprise Guide and through a remote SAS environment which might create complication.
I can import a csv file but have problem importing an xlsx file:
Successful:
%let Path_i1="\\awnjpsas02\Corporate-Asia\Production\SG\inputs\sasinputs.csv";
proc import datafile= &Path_i1 out=lob dbms=csv replace; GETNAMES=YES;
Failed:
%let Path_i1="\\awnjpsas02\Corporate-Asia\Production\SG\inputs\sasinputs.xlsx";
proc import datafile= &Path_i1 out=lob dbms=excel replace; sheet="lob";
Both codes work in my Base SAS. Are there any differences in coding between Base SAS and SAS Enterprise Guide?
Thanks a million.
Do you get an error message?
My first guess would be that you don't have a license for SAS/ACCESS to PC FIle Formats on your server.
As you're now using SAS on a remote system, perhaps with a 64-bit version of SAS, @Reeza might be correct. You might be able to use DBMS=XLSX with Excel data now -- that works well in SAS 9.4, and it insulates you from bit-architecture issues.
You might also find this paper helpful:
Feeling Anxious about Transitioning from Desktop to Server?
Chris
Hi @Reeza@ChrisHemedinger@LinusH
These are the error messages I received running on different options. Is there any other solution to the 64-bit/32-bit compatibility?
%let Path_i1="\\awnjpsas02\Corporate-Asia\Production\SG\inputs\sasinputs.xlsx";
proc import datafile=&Path_i1 out=lob dbms=excel replace; sheet="lob";
ERROR: Failed to connect to the Server: .
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
%let Path_i1="\\awnjpsas02\Corporate-Asia\Production\SG\inputs\sasinputs.xlsx";
proc import datafile=&Path_i1 out=lob dbms=excelcs replace; sheet="lob";
ERROR: Failed to connect to the Server: .
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
%let Path_i1="\\awnjpsas02\Corporate-Asia\Production\SG\inputs\sasinputs.xlsx";
proc import datafile=&Path_i1 out=lob dbms=pcfiles replace; sheet="lob";
ERROR: DBMS type PCFILES not valid for import.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
proc import out=lob datafile="\\awnjpsas02\Corporate-Asia\Production\SG\inputs\sasinputs.xlsx" dbms=excel replace; sheet='lob';
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
@ChrisHemedinger wrote a blog post about "64 bit gotchas" you may find useful at http://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/
Also this blog post - http://blogs.sas.com/content/sasdummy/2012/02/11/export-excel-methods/
Kind Regards,
Michelle
Did you try DBMS=XLSX? That option was added in a later 9.3 release, and works well in SAS 9.4.
I suggest you confirm that your EG remote SAS server has SAS/ACCESS for PC FILES installed and licensed first. Without this importing Excel will not work. In the EG server list you can select Properties on SASApp, select Software then SAS Server Products.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.