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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.