DATA Step, Macro, Functions and more

Importing a .xlsx file into SAS

Reply
Occasional Contributor
Posts: 15

Importing a .xlsx file into SAS

using sas 9.4 and unable to import data from .xlsx file 
error: file is not available 
same code is working for csv and .xls file 
 
filename exldata dde "excel|C:\Users\astha.verma\Desktop\SELF\jigsaw\SAS\SAS data sets\[Loandetails.xlsx] Sheet1!r2c1:r12c6" notab;
 
data exldata1;
infile exldata delimiter = '09'x dsd missover;
length name position $20.;
informat DOJ mmddyy10. DOB mmddyy9.;
format DOJ mmddyy10. DOB mmddyy9.;
input Name $ Income DOJ DOB Position $ Workex;
run;
Super User
Posts: 17,819

Re: Importing a .xlsx file into SAS

Is there a specific reason you're using DDE to import Excel data? 

 

And what doesn't work? You haven't explained how it doesn't work...

Occasional Contributor
Posts: 15

Re: Importing a .xlsx file into SAS

I am able to immport when file is in .xls format but it is not working on .xlsx file, as i want to connect sas with .xlsx with the help of DDE

PROC Star
Posts: 7,363

Re: Importing a .xlsx file into SAS

Maybe: Is the file already opened in Excel?

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 15

Re: Importing a .xlsx file into SAS

I am able to immport when file is in .xls format but it is not working on .xlsx file, as i want to connect sas with .xlsx with the help of DDE

Super User
Posts: 17,819

Re: Importing a .xlsx file into SAS

Not working means absolutely nothing. Please include more information. Did the file open in Excel? Was there any errors? In Excel or SAS? 

Occasional Contributor
Posts: 15

Re: Importing a .xlsx file into SAS

I am getting error that file is not available. I think it means sas is not able to connect to the .xlsx file

my code is working with .xls file and with .csv file 

Super User
Posts: 6,936

Re: Importing a .xlsx file into SAS

POST.THE.LOG.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 17,819

Re: Importing a .xlsx file into SAS

You never addressed why you would use DDE? I assume it's because if licensing issues? 

 

You dont need the DDE for the CSV file. 

 

For the XLSX file what happens when you run the connection string alone? I'm assuming it doesn't connect and Excel doesn't open so work on fixing the DDE statement. What version of Excel do you have? If it's 2013+ I have heard cases where DDE was no longer functional. 

 

Look at the different DDE options for connections. 

 

Post a screenshot of the error you receive. 

Super User
Super User
Posts: 7,401

Re: Importing a .xlsx file into SAS

As others have stated there is not enough information for us to debug.  What I would however state is that DDE is old - very old.  It was discontinued over 10 years ago and is really not recommended for use on anything now.  In certain setups it doesn't work at all.  I would really advise that you change your data handling policy, for instance is there a reason why you read in the data this way, rather than saving the data to CSV, there doesn't appear to be any funky Excel specific things going on as you are reading from first data row/column to end, so no reason to try any Excel specific things.  Depending on the file you may even get away with just:

libname tmp xlsx "<path to your file><yourfile>.xlsx";

 

Ask a Question
Discussion stats
  • 9 replies
  • 183 views
  • 1 like
  • 5 in conversation