topic Re: Importing a Microsoft Excel Worksheet (.xlsx) dataset into SAS 9.4 in SAS Programming

Importing a Microsoft Excel Worksheet (.xlsx) dataset into SAS 9.4

lgutie1808 — Wed, 04 Oct 2023 20:02:15 GMT

Hello,

I'm using the following code to import a Microsoft Excel Worksheet (.xls) dataset into SAS 9.4:

proc import out=Sleep

datafile="H:\NIAGEN Clinical Trial\Fellowship Project\Datasets\SAS Programs for Data Analysis"

DBMS=EXCEL REPLACE;

RANGE="Sheet1$";

GETNAMES=YES;

MIXED=YES;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

run;

However, I keep getting this error message in the Log:

<<ERROR: Unable to open file H:\NIAGEN Clinical Trial\Fellowship Project\Datasets\SAS Programs
for Data Analysis.XLS. It does not exist or it is already opened exclusively by
another user, or you need permission to view its data.>>

I don't have the file open when I'm running this code.

Many thanks in advance for some advice!

Re: Importing a Microsoft Excel Worksheet (.xlsx) dataset into SAS 9.4

Reeza — Wed, 04 Oct 2023 20:13:30 GMT

You're running SAS 9.4 on your local computer?

If you navigate to the path using the file explorer you can see the file?

Re: Importing a Microsoft Excel Worksheet (.xlsx) dataset into SAS 9.4

lgutie1808 — Wed, 04 Oct 2023 20:26:57 GMT

I'm getting access to the software in my local computer through VPN and the use of a remote desktop. H: is my workplace drive.

Yes! I can see the file when I navigate to the path using the file explorer.

Thank you.

Re: Importing a Microsoft Excel Worksheet (.xlsx) dataset into SAS 9.4

Reeza — Wed, 04 Oct 2023 20:30:59 GMT

What is the name of the Excel file?
SAS programs for data analysis seems like a folder name not an Excel file name but I've seen worse....

Re: Importing a Microsoft Excel Worksheet (.xlsx) dataset into SAS 9.4

Kurt_Bremser — Wed, 04 Oct 2023 20:36:05 GMT

The same network drive has to be mounted as H: on the SAS server for this to work.

Re: Importing a Microsoft Excel Worksheet (.xlsx) dataset into SAS 9.4

lgutie1808 — Wed, 04 Oct 2023 20:37:00 GMT

Thanks for noting that!

I have tried this code adding the name of the excel file and it worked!

proc import datafile = 'H:\NIAGEN Clinical Trial\Fellowship Project\Datasets\SAS Programs for Data Analysis\COVID_Niagen_Sleep_SAS_1.0.xlsx'
out = COVID.Sleep
dbms = xlsx
replace;
run;

Thank you!

Re: Importing a Microsoft Excel Worksheet (.xlsx) dataset into SAS 9.4

Kurt_Bremser — Wed, 04 Oct 2023 20:37:42 GMT

And use the XLSX engine and the complete filename with the .xlsx extension.

Re: Importing a Microsoft Excel Worksheet (.xlsx) dataset into SAS 9.4

lgutie1808 — Wed, 04 Oct 2023 20:39:27 GMT

Thanks for both comments.

I've added the name of the excel file and it worked!

The successful code is provided below:

proc import datafile = 'H:\NIAGEN Clinical Trial\Fellowship Project\Datasets\SAS Programs for Data Analysis\COVID_Niagen_Sleep_SAS_1.0.xlsx'
out = COVID.Sleep
dbms = xlsx
replace;
run;

Thank you!