How to read multiple excel sheets from a single excel file
at once????
- 7 Answers
- 15804 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 is the pound sign used for in the data_null_ ?
what are methods to identify duplicate observations?
what is treatment emergent events and treatment emregent adverse event
What are the prime responsibilities of data integration administrator? : Sas-di
what is the difference between unique key and primary key? : Sas-di
How can a SAS WEB REPORT STUDIO USER identify which report tabs they have been permitted without entering the SAS WEB REPORT STUDIO
How does SAS handle missing values in: assignment statements, functions, a merge, an update, sort order, formats, PROCs?
Are you involved in writing the inferential analysis plan? Tables specifications?
what do you mean by data staging area? : Sas-di
Where do you use proc means over proc freq?
What are the differences between proc means and proc summary?
What is the difference between class statement and by statement in proc means?
Teradata 
