excel

Accepted Solution Solved
Reply
Valued Guide
Posts: 858
Accepted Solution

excel

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


Accepted Solutions
Solution
‎03-01-2013 09:22 AM
Super Contributor
Posts: 1,636

Re: excel

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


All Replies
Super Contributor
Posts: 1,636

Re: excel

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

Valued Guide
Posts: 858

Re: excel

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

Solution
‎03-01-2013 09:22 AM
Super Contributor
Posts: 1,636

Re: excel

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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