BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Steelers_In_DC
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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.

View solution in original post

3 REPLIES 3
Linlin
Lapis Lazuli | Level 10

do you want to import a specific sheet or specific cells?

Steelers_In_DC
Barite | Level 11

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!!

Linlin
Lapis Lazuli | Level 10

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 468 views
  • 0 likes
  • 2 in conversation