Why does it tell me "ERROR: Physical file does not exist, EXCEL|C:\Users\peter.ahn\Desktop\vol70_test\Output\tab002.xlsx\Table 1!R5C1:R101C5." even though the Excel file DOES exist in my target folder? It was working fine on another table using same syntaxes and commands. (The only difference in that was using 001 instead of 002.) I'm trying to spit out my final dataset into a specific range of rows and columns of my target Excel file.
proc sql;
Create table table002 as select distinct
roworder, _&py, _&cy, _&ny, pchange
from table002_roworder a left join pre_fin b on a.varname=b.varname
order by roworder;
quit;
proc contents data=table002(drop=roworder) out=varlist noprint;
proc sort; by varnum; run;
%array(cord,data=varlist,var=name)
data _null_;
set table002;
call symputx('nrows',_n_);
run;
%let outfile=C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx;
OPTIONS NOXWAIT NOXSYNC MISSING=' ';
x '"C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx"';
/*To let the file to open allow SAS to sleep for 5 seconds*/
data _null_;
x=sleep(5);
run;
%let firstrow=6;
%let firstcol=7;
%let lastrow=%eval(&firstrow+&nrows-1);
%let lastcol=%eval(&firstcol+&cordn-1);
filename t002 dde "EXCEL|&outfile\Table 1!R&firstrow.C&firstcol.:R&lastrow.C&lastcol." notab;
data _null_;
set table002;
file t002;
put %do_over(cord,between=%str('09'x),phrase=?);
run;
29 proc sql;
30 Create table table002 as select distinct
31 roworder, _&py, _&cy, _&ny, pchange
32 from table002_roworder a left join pre_fin b on a.varname=b.varname
33 order by roworder;
NOTE: Table WORK.TABLE002 created, with 97 rows and 5 columns.
34 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
35
36
37 proc contents data=table002(drop=roworder) out=varlist noprint;
NOTE: The data set WORK.VARLIST has 4 observations and 41 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
38 proc sort; by varnum; run;
NOTE: There were 4 observations read from the data set WORK.VARLIST.
NOTE: The data set WORK.VARLIST has 4 observations and 41 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
2 The SAS System 16:35 Saturday, October 1, 2022
cpu time 0.00 seconds
39 %array(cord,data=varlist,var=name)
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: There were 4 observations read from the data set WORK.VARLIST.
NOTE: CALL EXECUTE routine executed successfully, but no SAS statements were generated.
40
41 data _null_;
42 set table002;
43 call symputx('nrows',_n_);
44 run;
NOTE: There were 97 observations read from the data set WORK.TABLE002.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
45
46 %let outfile=C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx;
47
48 OPTIONS NOXWAIT NOXSYNC MISSING=' ';
49 x '"C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx"'
49 ! ;
50 /*To let the file to open allow SAS to sleep for 5 seconds*/
51 data _null_;
52 x=sleep(5);
53 run;
NOTE: DATA statement used (Total process time):
real time 5.00 seconds
cpu time 0.00 seconds
54
55 %let firstrow=6;
56 %let firstcol=7;
57
58 %let lastrow=%eval(&firstrow+&nrows-1);
59 %let lastcol=%eval(&firstcol+&cordn-1);
60
61 filename t002 dde "EXCEL|&outfile\Table 1!R&firstrow.C&firstcol.:R&lastrow.C&lastcol." notab;
62
63 data _null_;
64 set table002;
65 file t002;
66 put %do_over(cord,between=%str('09'x),phrase=?);
67 run;
ERROR: Physical file does not exist, EXCEL|C:\Users\peter\Desktop\vol70_test\Output\tab002.xlsx\Table 1!R6C7:R102C10.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
