I am trying to use a DDE connection to import an Excel spreadsheet into SAS. I have the following code:
options noxwait noxsync; %sysexec "start excel"; data _null_; rc=sleep(15); run; data HouseTables.England (keep=LAD AREA_NAME _&MYEPrev.-_&FPY.); informat LAD $9. AREA_NAME $35. AREA_NAME1 $35. AREA_NAME2 $35. AREA_NAME3 $35. _1991-_&FPY. comma16.; filename Excel dde "Excel|&rootdir.\Input\[Household_Projections.xlsx]406!R6C1:R410C53" notab; infile Excel lrecl=10001 dsd missover delimiter='09'x firstobs=1; input LAD AREA_NAME1 AREA_NAME2 AREA_NAME3 _1991-_&FPY. run;
The script ran perfectly on somebody else's PC. But running it on mine, I get the following error:
ERROR: Failed to connect to Excel|[Hidden Excel file path]\[Household_Projections.xlsx]406!R6C1:R410C53 : The error DMLERR_NO_CONV_ESTABLISHED was returned
Can anybody help with this? Excel is definitely up and running after running the first bit of code, but it won't seem to connect.
Update: this is now solved!
I used the %sysexec command to open the specific Excel i was using, and then I have to Enable Editing on the document.
Update: this is now solved!
I used the %sysexec command to open the specific Excel i was using, and then I have to Enable Editing on the document.
DDE is legacy and even still supported you really shouldn't use it anymore.
https://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/
@C20325A wrote:
Update: this is now solved!
I used the %sysexec command to open the specific Excel i was using, and then I have to Enable Editing on the document.
There are several other programs out there that will interfere with DDE connections to Excel. If the only purpose is to read the file there are many other ways that are more reliable.
Sorry, I don't recognise the error and its not worth my time debugging this. Basically DDE is over 15 years out of support, it is ancient, and in quite a lot of scenarios does not work, or does not have the functionality needed. There are many ways to get data into SAS, for instance you could start with a proc import, this guesses the data though so not ideal (better to save to CSV and write a datastep, far more robust), or there is, if you have > V9.3 libname excel.
So yes, start by trying proc import - you can specify range - this should be near enough and will work on a broader spectrum (unless the machine does not have Office on it, then you need pcfiles).
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.
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.