topic Re: how to transfer excel to SAS data set in SAS Programming

how to transfer excel to SAS data set

tianerhu — Mon, 20 Jun 2022 21:15:36 GMT

There are 12 sheets(from lab1 to lab12) in one excel file.

I want to transfer lab1 to sas data set . The program is following :

libname certadv xlsx 'C:\SAS data and program\data\data set used in OG of SAS adv\certadv\jijing.xlsx';
data certadv.lab1;
set certadv.'Lab1'n;
run;
proc print data = certadv.lab1;
run;

There are error note in the log:

67 libname certadv xlsx 'C:\SAS data and program\data\data set used in OG of SAS
67 ! adv\certadv\jijing.xlsx';
NOTE: Libref CERTADV was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\SAS data and program\data\data set used in OG of SAS
adv\certadv\jijing.xlsx
68 data certadv.lab1;
69 set certadv.'Lab1'n;
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File CERTADV.Lab1.DATA does not exist.
70 run;

There are no variables for the output file
ERROR: File CERTADV.lab1.DATA does not exist.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set CERTADV.lab1 was only partially opened and will not be saved.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

71 proc print data = certadv.lab1;
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File CERTADV.lab1.DATA does not exist.
72 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

******************************************************************************

Please tell me how to correct my program , Thank you .

Re: how to transfer excel to SAS data set

Reeza — Mon, 20 Jun 2022 21:37:46 GMT

data work.lab1;
set certadv.'Lab1'n;
run;

Try the following. For some reason, it doesn't think that the file has the sheet LAB1. Note that you also assigned the output of the data step it to the CERTADV file, which means you have file being written back to Excel. You probably want that to be WORK instead.

Because a LIBNAME can be used to create or export files the fact that the library was created does not mean that the file path was correct.

Verify the file path and ensure it's correct and that the sheetname is exactly Lab1 with no spaces or other capitalizations.

Re: how to transfer excel to SAS data set

tianerhu — Mon, 20 Jun 2022 22:03:36 GMT

Thank you for your help;

Re: how to transfer excel to SAS data set

Kurt_Bremser — Tue, 21 Jun 2022 06:10:42 GMT

Run this:

libname certadv xlsx 'C:\SAS data and program\data\data set used in OG of SAS adv\certadv\jijing.xlsx';

proc sql;
select memname from dictionary.tables
where libname = "CERTADV";
quit;

Is your Excel file located on your PC? If you use SAS on a remote server, you cannot access your C: drive like that.