I ran across a certification prep question that I do not know the answer too. I don't have the exact question but it related to referencing a specific excel sheet in sas code. During the study quiz I guessed that it was similar to what I have seen in excel (=Sheet2!B1) but I can't find any sas documentation confirming/denying this. If anyone has any sample code, or can write an example of how to import and export to a specific sheet I would greatly appreciate it.
Thank,
Mark
Hi Mark,
Below is an example using libname to create and read excel files.
/* create excel file class.xls from dataset sashelp.class */
libname test "C:\TEMP\forum\class.xls";
data test.names;
set sashelp.class;
run;
libname test clear;
/* create sas dataset new_class from excel file class.xls */
libname ttt "C:\TEMP\forum\class.xls";
data new_class;
set ttt."names"n;
run;
proc print;title &syslast;run;
libname ttt clear;
/* only import some cells of the excel file */
libname www "C:\TEMP\forum\class.xls" getnames=no;
data only_specific_cells;
set www."names$a8:c18"n;
run;
proc print;title &syslast;run;
libname www clear;
/* log file */
46
47 /* create excel file class.xls from dataset sashelp.class */
48 libname test "C:\TEMP\forum\class.xls";
NOTE: Libref TEST was successfully assigned as follows:
Engine: EXCEL
Physical Name: C:\TEMP\forum\class.xls
49 data test.name;
50 set sashelp.class;
51 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set TEST.name has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
52 libname test clear;
NOTE: Libref TEST has been deassigned.
53
54 /* create sas dataset new_class from excel file class.xls */
55 libname ttt "C:\TEMP\forum\class.xls";
NOTE: Libref TTT was successfully assigned as follows:
Engine: EXCEL
Physical Name: C:\TEMP\forum\class.xls
56 data new_class;
57 set ttt."name"n;
58 run;
NOTE: There were 19 observations read from the data set TTT.name.
NOTE: The data set WORK.NEW_CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
59 proc print;title &syslast;run;
NOTE: There were 19 observations read from the data set WORK.NEW_CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
60 libname ttt clear;
NOTE: Libref TTT has been deassigned.
61 /* only import some cells if the excel file */
62 libname www "C:\TEMP\forum\class.xls" getnames=no;
NOTE: Libref WWW was successfully assigned as follows:
Engine: EXCEL
Physical Name: C:\TEMP\forum\class.xls
63 data only_specific_cells;
64 set www."name$a8:c18"n;
65 run;
NOTE: There were 11 observations read from the data set WWW.'name$a8:c18'n.
NOTE: The data set WORK.ONLY_SPECIFIC_CELLS has 11 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
66 proc print;title &syslast;run;
NOTE: There were 11 observations read from the data set WORK.ONLY_SPECIFIC_CELLS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
67 libname www clear;
NOTE: Libref WWW has been deassigned.
do you want to import a specific sheet or specific cells?
Linlin,
I was inquiring about importing and exporting a specific sheet, but if you could include information about a specific cell too that would be amazing!!
Hi Mark,
Below is an example using libname to create and read excel files.
/* create excel file class.xls from dataset sashelp.class */
libname test "C:\TEMP\forum\class.xls";
data test.names;
set sashelp.class;
run;
libname test clear;
/* create sas dataset new_class from excel file class.xls */
libname ttt "C:\TEMP\forum\class.xls";
data new_class;
set ttt."names"n;
run;
proc print;title &syslast;run;
libname ttt clear;
/* only import some cells of the excel file */
libname www "C:\TEMP\forum\class.xls" getnames=no;
data only_specific_cells;
set www."names$a8:c18"n;
run;
proc print;title &syslast;run;
libname www clear;
/* log file */
46
47 /* create excel file class.xls from dataset sashelp.class */
48 libname test "C:\TEMP\forum\class.xls";
NOTE: Libref TEST was successfully assigned as follows:
Engine: EXCEL
Physical Name: C:\TEMP\forum\class.xls
49 data test.name;
50 set sashelp.class;
51 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set TEST.name has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
52 libname test clear;
NOTE: Libref TEST has been deassigned.
53
54 /* create sas dataset new_class from excel file class.xls */
55 libname ttt "C:\TEMP\forum\class.xls";
NOTE: Libref TTT was successfully assigned as follows:
Engine: EXCEL
Physical Name: C:\TEMP\forum\class.xls
56 data new_class;
57 set ttt."name"n;
58 run;
NOTE: There were 19 observations read from the data set TTT.name.
NOTE: The data set WORK.NEW_CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
59 proc print;title &syslast;run;
NOTE: There were 19 observations read from the data set WORK.NEW_CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
60 libname ttt clear;
NOTE: Libref TTT has been deassigned.
61 /* only import some cells if the excel file */
62 libname www "C:\TEMP\forum\class.xls" getnames=no;
NOTE: Libref WWW was successfully assigned as follows:
Engine: EXCEL
Physical Name: C:\TEMP\forum\class.xls
63 data only_specific_cells;
64 set www."name$a8:c18"n;
65 run;
NOTE: There were 11 observations read from the data set WWW.'name$a8:c18'n.
NOTE: The data set WORK.ONLY_SPECIFIC_CELLS has 11 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
66 proc print;title &syslast;run;
NOTE: There were 11 observations read from the data set WORK.ONLY_SPECIFIC_CELLS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
67 libname www clear;
NOTE: Libref WWW has been deassigned.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.