ERROR Physical file does not not

[juju_p]

Dear all,

I am experiencing an issue with the following statement to open an Excel file with SAS 9.4

I do not understand why SAS says that the physical file does not exist although it is well there

CODE

options noxwait noxsync;
x '"C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE"';
data _null_;
x=sleep(5);
run;

/* Open Excel workbook automatically */
filename ddecmd dde 'excel|system';
DATA _null_;
file ddecmd;

/* Adapt Path and name of the input excel file */
put '[OPEN("S:\TRIALS\AES\AES1\DBASE\DBASE_EXT\ADA\FU_PERIOD\TROIKA_ADA_FU_22Jan2022_bis.xlsx")]';
RUN;

/* Adapt Path and name of the input excel file */
filename file dde 'excel|S:\TRIALS\AES\AES1\DBASE\DBASE_EXT\ADA\FU_PERIOD\[TROIKA_ADA_FU_22Jan2022_bis.xlsx]DTAformat!R2C1:R1000C13' lrecl=500;

/*Create SAS dataset for external data*/

DATA ada;
INFILE file dlm='09'x notab dsd missover ls=1500;
informat PROTOCOL $6. AGILEX $3. SUBJID $11. BARCODE $11. VISITT $20. BIOMATRIX $9. ANLYTE $16. SCROUT $8. CONFOUT $8. TITROUT 3. RESCO $10. ;
input PROTOCOL $ AGILEX $ SUBJID $ BARCODE $ VISITT $ BIOMATRIX $ ANLYTE $ SCROUT $ CONFOUT $ TITROUT RESCO $ ;
/* IF visitt = 'FU 1 Year' then visitt='1 Year FU';*/
RUN;

ERROR MESSAGE

ERROR: Physical file does not exist,
excel|S:\TRIALS\AES\AES1\DBASE\DBASE_EXT\ADA\FU_PERIOD\[TROIKA_ADA_FU_22Jan2022_bis.xlsx]DTAformat!R2C1
:R1000C13.

[LinusH]

Can't tell why this does not work for you, but DDE is discontinued technology.

If your task is to import Excel data to SAS, I suggest that you use any of the many other methods to do so.

[Kurt_Bremser]

Since DDE is deprecated and may not work any longer, I'd use VBA to load the spreadsheet and save it to a tab-separated file, which can then be read as a physical file in the data step.

[juju_p]

It happened to work for me when I copied the whole content of the file into another file so it probably has to do with the properties of the original file

I was wondering how could we do it as well with VBA?

[Kurt_Bremser]

---

@juju_p wrote:

I was wondering how could we do it as well with VBA?

---

That needs to be answered by some Windows-savvy people. I neither use Excel files, nor run SAS on Windows 😉

[AMSAS]

There are multiple ways to read Excel files into SAS e.g. Comparing SAS LIBNAME Engines for PC Files Data