BookmarkSubscribeRSS Feed
mrinmoy
Obsidian | Level 7
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;
9 REPLIES 9
Reeza
Super User

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

mrinmoy
Obsidian | Level 7

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

art297
Opal | Level 21

Maybe: Is the file already opened in Excel?

 

Art, CEO, AnalystFinder.com

 

mrinmoy
Obsidian | Level 7

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

Reeza
Super User

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

mrinmoy
Obsidian | Level 7

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 

Reeza
Super User

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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";

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 1467 views
  • 1 like
  • 5 in conversation