SAS Life Science Analytics Framework and the clinical data products from SAS

Reading Excel worksheets in SAS Drug Development

Reply
SAS Employee
Posts: 10

Reading Excel worksheets in SAS Drug Development

  Users often need additional assistance with reading Excel worksheets in SDD.

In 4.x instances of SAS Drug Development (SDD), you can access Excel spreadsheets the same way you would on a PC.  Here’s an example that creates a SAS data set named “XL_1” from the “Sheet1” worksheet in the “AE_Specs2.xlsx” workbook:


%let workbook=&_sasws_/QAGPharma/Study2/Files/documents/AE_specs2;
PROC IMPORT
   DATAFILE="&workbook"
      DBMS=xlsx
      REPLACE
      OUT=xl_1;
   SHEET="Sheet1";
run;


In 3.x instances, the rules are different.  You can convert spreadsheets to SAS data sets using the Advanced Loader. To read a spreadsheet within a program, you’ll need to remote submit a PROC Import step or a LIBNAME statement, as in this example, which converts one worksheet (identified by the SHEETN Text parameter) in an Excel workbook (identified by the INXLS Input File parameter) to a SAS data set:


signon;

   data _null_;
      call execute('rsubmit;');
      call execute('data _null_;');
      call execute('call symput("inxls","' || pathname("&inxls") || '");');
      call execute('call symput("sheetn","' || "&sheetn" || '");');
      call execute('run;');
      call execute('endrsubmit;');
   run;

   rsubmit;

      data _null_;
      call execute('filename outxls "' || pathname("work") || '/temp.xls";');
      run;

      proc upload infile="&inxls"
                  outfile=outxls
                  binary;
      run;

      proc import out=r_data
                  datafile=outxls 
                  dbms=xls
                  replace;
           sheet="&sheetn";
      run;

      proc download data=r_data
                    out=WORK.xls_srce;
      run;

   endrsubmit;

signoff;

This is how we usually solve this challenge.  Do you use this same approach or a different one?

Ask a Question
Discussion stats
  • 0 replies
  • 388 views
  • 0 likes
  • 1 in conversation