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
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.
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.
Are you running in any sort of SAS server version? If so does the SERVER see the same drive N as you do?
yeah, why u ask so ?
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.