topic Re: SAS EG & DDE with Excel in SAS Enterprise Guide

SAS EG & DDE with Excel

RajD — Mon, 23 Jun 2014 11:18:38 GMT

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.

Re: SAS EG & DDE with Excel

LinusH — Mon, 23 Jun 2014 11:27:57 GMT

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?

Re: SAS EG & DDE with Excel

RajD — Mon, 23 Jun 2014 17:35:14 GMT

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

Re: SAS EG & DDE with Excel

SASKiwi — Mon, 23 Jun 2014 19:34:55 GMT

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

Re: SAS EG & DDE with Excel

tammy_dezilva — Mon, 23 Jun 2014 23:54:39 GMT

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;

Re: SAS EG & DDE with Excel

RajD — Tue, 24 Jun 2014 04:03:46 GMT

Hi All,

Thank you X command open the Excel file.

Re: SAS EG & DDE with Excel

yaswanthkumarreddy_kalluru_techmahindra — Thu, 23 Jul 2015 03:15:26 GMT

Hi RajD ... can you please suggest us how you resolved this issue of working DDE in SAS EG

Re: SAS EG & DDE with Excel

yaswanthkumarreddy_kalluru_techmahindra — Thu, 23 Jul 2015 03:17:53 GMT

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.

Re: SAS EG & DDE with Excel

SASKiwi — Thu, 23 Jul 2015 04:51:00 GMT

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.

Re: SAS EG & DDE with Excel

Cynthia_sas — Sun, 26 Jul 2015 23:54:42 GMT

Hi:

SASKiwi already gave you the answer when he said (highlights mine):

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