topic Re: Importing a .xlsx file into SAS in SAS Programming

Importing a .xlsx file into SAS

mrinmoy — Wed, 19 Apr 2017 03:30:35 GMT

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;

Re: Importing a .xlsx file into SAS

Reeza — Wed, 19 Apr 2017 03:45:21 GMT

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

Re: Importing a .xlsx file into SAS

art297 — Wed, 19 Apr 2017 03:46:38 GMT

Maybe: Is the file already opened in Excel?

Art, CEO, AnalystFinder.com

Re: Importing a .xlsx file into SAS

mrinmoy — Wed, 19 Apr 2017 04:48:54 GMT

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

Re: Importing a .xlsx file into SAS

mrinmoy — Wed, 19 Apr 2017 04:49:30 GMT

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

Re: Importing a .xlsx file into SAS

Reeza — Wed, 19 Apr 2017 05:27:01 GMT

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

Re: Importing a .xlsx file into SAS

mrinmoy — Wed, 19 Apr 2017 05:45:08 GMT

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

Re: Importing a .xlsx file into SAS

Kurt_Bremser — Wed, 19 Apr 2017 07:28:15 GMT

POST.THE.LOG.

Re: Importing a .xlsx file into SAS

RW9 — Wed, 19 Apr 2017 08:18:49 GMT

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

Re: Importing a .xlsx file into SAS

Reeza — Wed, 19 Apr 2017 13:26:30 GMT

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.