Importing a .xlsx file into SAS

[mrinmoy]

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;

[Reeza]

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]

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]

Maybe: Is the file already opened in Excel?

Art, CEO, AnalystFinder.com

[mrinmoy]

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]

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

[mrinmoy]

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 

[Kurt_Bremser]

POST.THE.LOG.

[Reeza]

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]

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