excel

Steelers_In_DC — Thu, 28 Feb 2013 21:04:56 GMT

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

Re: excel

Linlin — Thu, 28 Feb 2013 21:09:50 GMT

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

Re: excel

Steelers_In_DC — Thu, 28 Feb 2013 21:15:44 GMT

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

Re: excel

Linlin — Fri, 01 Mar 2013 14:22:48 GMT

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.

topic Re: excel in SAS Programming

excel

Re: excel

Re: excel

Re: excel