BookmarkSubscribeRSS Feed
RajD
Calcite | Level 5

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.

9 REPLIES 9
LinusH
Tourmaline | Level 20

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?

Data never sleeps
RajD
Calcite | Level 5

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

SASKiwi
PROC Star

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

RajD
Calcite | Level 5

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.

SASKiwi
PROC Star

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.

tammy_dezilva
Quartz | Level 8

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

Cynthia_sas
SAS Super FREQ

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

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 3743 views
  • 0 likes
  • 6 in conversation