Desktop productivity for business analysts and programmers

SAS EG & DDE with Excel

Reply
New Contributor
Posts: 3

SAS EG & DDE with Excel

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.

Esteemed Advisor
Posts: 5,202

Re: SAS EG & DDE with Excel

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
New Contributor
Posts: 3

Re: SAS EG & DDE with Excel

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

Respected Advisor
Posts: 3,068

Re: SAS EG & DDE with Excel

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

New Contributor
Posts: 3

Re: SAS EG & DDE with Excel

Hi All,

Thank you X command open the Excel file.

Re: SAS EG & DDE with Excel

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.

Respected Advisor
Posts: 3,068

Re: SAS EG & DDE with Excel

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.

Contributor
Posts: 47

Re: SAS EG & DDE with Excel

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

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

SAS Super FREQ
Posts: 8,721

Re: SAS EG & DDE with Excel

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

Ask a Question
Discussion stats
  • 9 replies
  • 1330 views
  • 0 likes
  • 6 in conversation