Interview Questions   Tutorials   Discussions   Programs   Videos   

SAS - How to read multiple excel sheets from a single excel file at once?




386
views
asked mar September 20, 2014 06:30 AM  

How to read multiple excel sheets from a single excel file at once?


           

1 Answers



 
answered By vishnoiprem   0  
LIBNAME GETIT EXCEL "test.xlsx" MIXED=NO SCAN_TIMETYPE=ANY ;
  DATA NEW ;
    SET GETIT.'FINALS$'n  ;
  RUN ;
LIBNAME GETIT CLEAR ; RUN ;

The available tools for this importation job include the IMPORT Wizard, PROC IMPORT, ODBC, PROC DBLOAD, PROC ACCESS, PROC SQL, and DDE. If we look at each of these tools, we find that we have to specify the different sheet names by hard coding them, in order to retrieve the sheet names from each workbook and pass them through a macro loop. Here is an example of hard coding a sheet name using PROC IMPORT.

proc import datafile=’C:\My Documents\Excel Files\Hospital1.xls’ 
 out= hospital1 replace; 
 sheet=’JAN, 2003’; 
 getnames=yes; 
run; 

This is an example of hard coding using DDE. Note that we need to specify variable names start and end numbers for the rows and columns, and some variable attributes. filename sheet dde "Excel|Jan, 2004!r2.c1:r5000.c4" NOTAB;

data _null_ ; 
 x=sleep (5); 
run; 
filename mydde dde "excel|system" ; 
data _null_ ; 
 file mydde ; 
 put "[open(‘C:\My Documents\Excel Files\Hospital1.xls’)]"; 
run; 
data H1200301; 
 infile sheet dsd dlm='09'x truncover; 
 length NDC $7 UNITS 8 AMMOUNT 8 DATE mmddyy8.; 
 input NDC UNITS AMMOUNT DATE; 
run; 
data _null_; 
 file mydde ; 
 put '[q
flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert