topic Re: Error using DDE to download Excel spreadshet in SAS Programming

Error using DDE to download Excel spreadshet

Shrij — Thu, 16 Aug 2018 09:48:29 GMT

filename PDfile DDE "Excel|N:\statistics\Pr 113-10\Safety Analysis\Data\External\[113-10_Updated_Master PD tracker_18 Jan 18.xlsx]Sheet1!R2C1:R139C15" notab;

data PD;
infile PDfile dlm='09'x dsd missover LRECL=150006;
input Sr_No:$3. ScreeningNo:$20. Subject_No:$6. Treatment:$5. Visit:$100. DateDeviation:$100. TimeDev:$20.
ProtocolSection:$20. Description:$800. TypeDev:$20. ProtocolDev:$50.
Reason:$500. Impact:$300. Corrective:$600. Preventive:$600.;
run;

Am trying to read this file the path in correct, no flaw in that but yet while running it say :-

ERROR: Physical file does not exist, Excel|[N:\statistics\Pr 113-10\Safety
Analysis\Data\External\113-10_Updated_Master PD tracker_18 Jan 18.xlsx]Sheet1!R2C1:R139C15.

What am i doing wrong

Re: Error using DDE to download Excel spreadshet

RW9 — Thu, 16 Aug 2018 10:16:51 GMT

Does the file exist in the location you specified. Anyways irrelevant, don't use DDE. It is ancient > 15 years out of support, doesn't have later Excel version functionalities, only works on one system, and really shouldn't be being used (although to be honest, avoiding Excel at all will help you no end). Proc import should read that ok, although using a guessing procedure isn't recommended either.

Get the data in a proper data transfer file format such as csv, then write a datastep to correctly import the data.

Re: Error using DDE to download Excel spreadshet

Kurt_Bremser — Thu, 16 Aug 2018 14:11:01 GMT

Have you made sure that Excel is up and running and has loaded the workbook?

See this example from a paper found at lexjansen.com:

*;
/* set options and invoke Excel using DDE */
*;
options noxwait noxsync;
x '"c:\program files\microsoft office\office11\excel.exe"';
data _null_;
x=sleep(5);
run;
*;
/* open Excel workbook */
*;
filename ddecmd dde 'excel|system';
data _null_;
file ddecmd;
put '[FILE-OPEN("c:\wuss\names workbook.xls")]';
run;
*;
/* specify desired Excel worksheet cell range */
*;
filename xlin DDE "excel|c:\wuss\[names workbook.xls]names worksheet!r2c1:r65536c6";
run;
*;
/* read Excel files using DDE into SAS data set*/
*;
data wuss.employee(label='EMPLOYEE ROSTER' index=(EMP_ID));
infile xlin dlm='09'x notab missover dsd;
informat FIRST $20. LAST $20. DEPT $4. EMP_ID $6. TITLE $30. RATE 8.2;
input FIRST LAST DEPT EMP_ID TITLE RATE;
format FIRST $20. LAST $20. DEPT $4. EMP_ID $6. TITLE $30. RATE 8.2;
label FIRST  = 'EMPLOYEE FIRST NAME'
LAST   = 'EMPLOYEE LAST NAME'
DEPT   = 'DEPARTMENT'
EMP_ID = 'EMPLOYEE ID'
TITLE  = 'EMPLOYEE TITLE'
RATE   = 'PAY RATE';
run;
*;
/* close Excel workbook and close Excel */
*;
8
data _null_;
file xlin;
put '[FILE-CLOSE("c:\wuss\names workbook.xls")]';
put ‘[QUIT()]’;
run;

The "physical file does not exist" will happen if Excel is not started, for instance.

Re: Error using DDE to download Excel spreadshet

ballardw — Thu, 16 Aug 2018 14:32:48 GMT

Are you running in any sort of SAS server version? If so does the SERVER see the same drive N as you do?

Re: Error using DDE to download Excel spreadshet

Shrij — Thu, 23 Aug 2018 12:12:16 GMT

yeah, why u ask so ?