How to read multiple excel sheets from a single excel file
at once????
- 7 Answers
- 15812 Views
- HCL, Verinon Technology Solutions, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / sravan
Using libname statement.
Libname <lib_name> excel 'file_specification';
| Is This Answer Correct ? | 5 Yes | 0 No |
Answer / rajesh
First you have to split the excel sheets and write the
program in macros and use the dde triplet.
| Is This Answer Correct ? | 3 Yes | 1 No |
Answer / paul
we can read by a simple libname access method:
eg:
libname exlbook 'd:\excel file location\file name.xls';
proc copy in=exlbook out=work;
run;
/*here i am coying the entire excel workbook to work library
in sas, so we can get all sheets at a time.
if we use proc import we can import a single sheet at a time
By the above method we can dump all table from a MS-access database or oracle database or any other db.
only we have to change the connection details, i.e.,
excel --> file name with path
access --> db name with path
oracle/any other RDBMS --> user=***, password=****,
path=**** or host=****
another advantage of this method is :
the library will not occupy any memory on hard disc
it only acts as a repository which save memory and
processing time */
| Is This Answer Correct ? | 1 Yes | 2 No |
Answer / ashish
%macro pim(sheet);
proc import out= payment
datafile = 'E:SAS DOCprojectCredit Banking.xls'
dbms = Excel;
sheet = "&sheet";
getnames = yes;
run;
%mend piim;
%pim(Customer Acqusition);
%pim(Spend);
%pim(Repayment);
| Is This Answer Correct ? | 0 Yes | 1 No |
Answer / lucylu
%macro read_moresheets(ifile, lib);
libname ixls excel &ifile;
proc sql;
select memname into :ds1 - :ds100
from sashelp.vtable
where libname = "IXLS" and index(memname,'$') = 0
;
quit;
%put _user_;
%do i = 1 %to &sqlobs;
data &lib..&&ds&i;
set ixls.&&ds&i;
run;
%end;
%mend;
%read_moresheets("C:\Ongoing\CallActivity.xls", work);
| Is This Answer Correct ? | 1 Yes | 3 No |
What are the default statistics for means procedure?
how do we get duplicate observations in a separate dataset?
Describe 5 ways to do a “table lookup” in SAS?
what is the function of catx syntax? : Sas-administrator
What are the difficulties u faced while doing vital signs table or dataset?
What Proc glm does?
1.we can execute a macro with in a macro,by using call symput and symget can any one give me one example? 2.We can create the macro variables by using %let,%do,macro parameters,INTO clause in proc sql and call symput, can any one give me example to create macro variable with INTO clause and call symput? 3.
If reading an external file to produce an external file, what is the shortcut to write that record without coding every single variable on the record?
if reading an external file to produce an external file, what is the shortcut to write that record without coding every single variable on the record
What would you change about your job?
What SAS statements would you code to read an external raw data file to a DATA step?
How would you delete observations with duplicate keys?
Teradata 
