Hi SAS Gurus, I am new to SAS Programming. Currently using SAS 9.3 EG and struggling with DDE & Excel.
I am trying to import / read data from Excel 2007. I am getting error during running the code and excel file is opening. Here is the code & error below:
Libname para 'c:\sasbaseprep';
/* Read an Excel spreadsheet using DDE */
OPTIONS NOXSYNC NOXWAIT;
X '"C:\SASBASEPrep\GASPRICESUS.XLSX"';
data _null_;
X=sleep(3);
run;
FILENAME gas DDE 'EXCEL|C:\SASBASEPrep\[GASPRICESUS.XLSX]Sheet1!A1:B51';
DATA para.readexcel;
INFILE gas NOTAB DLM='09'X DSD MISSOVER;
informat Avg_price 4.3;
INPUT US_State $ 1-30 Avg_Price 4.3;
format Avg_price 4.3;
RUN;
Proc Print data=para.readexcel;
title 'Read / Import data from MS Excel';
run;
data _null_;
file gas;
put '[FILE-CLOSE("C:\SASBASEPrep\GASPRICESUS.XLSX")]';
put '[QUIT()]';
run;
ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the DATA step program.
Aborted during the EXECUTION phase.
NOTE: 1 record was read from the infile GAS.
The minimum record length was 0.
The maximum record length was 0.
NOTE: The SAS System stopped processing this step because of errors.
Can anyone please help me what is wrong with the code and how to fix it. I tried unchecking the option in Excel "Ignore other applications that use DDE", but no success.
Thanks in advance
Raj.
DDE is not support3d by MS any more.
DDE is not possible to use from within EG.
My recommendation is that you try to find som e other way to achieve your requirement.
Are EG communicating witha local or a remote SAS server?
Thank you. EG is communicating with local server.
Also I tried running the same code in SAS BASE and getting the same error.
ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the DATA step program. Aborted during the EXECUTION phase.
Any further help please.. Thanks in Advance
- Raj
Does your X command actually open an Excel window or not? If you don't see Excel then DDE isn't going to work.
I suspect your X command should look more like this:
X 'Excel "C:\SASBASEPrep\GASPRICESUS.XLSX" ';
Hi All,
Thank you X command open the Excel file.
Hi Raj,
Can you please suggest us .. How you resolved this issue -Working DDE commands in SAS EG,
I am unable to open the EXCEL with the same query in SAS EG.
DDE using EG is only possible if you are using a local SAS server. That means you are using SAS on the same PC as EG. If you use a remote SAS server DDE is not possible, as Excel cannot be controlled remotely via DDE.
Hi Raj
I was randomly getting DDE error messages and have found the most stable method was to firstly start excel (via SAS) and then open the file using DDE commands with ample sleep time between commands.
x '"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"';
data _null_;
x=sleep(3);
run;
filename DDEcmds dde "excel|system";
data _null_;
file DDEcmds;
length dde_cmd $200;
dde_cmd='[open("c:\sample.xlsx")]';
put dde_cmd;
x=sleep(3);
run;
Hi RajD ... can you please suggest us how you resolved this issue of working DDE in SAS EG
Hi:
SASKiwi already gave you the answer when he said (highlights mine):
DDE using EG is only possible if you are using a local SAS server. That means you are using SAS on the same PC as EG. If you use a remote SAS server DDE is not possible, as Excel cannot be controlled remotely via DDE.
For some more insight about EG and DDE, please see this blog posting: http://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/
cynthia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.