multiple sheets in excel

[TarunKumar]

Hi,

I have multiple excel sheet. i want to import all sheets with the help Of macro.

[allurai0412]

hi ,

libname engine will also helps you ...for importing ....

but try this .....i have excel (Y3DTR.XLS) having two sheets named PURCHASE , SWITCHES

%MACRO IMPR(IMP,OUT_DAT,EXL_SHT);

PROC IMPORT DATAFILE="&IMP" DBMS=EXCEL OUT=&OUT_DAT REPLACE; /*for oupput dataset for each SHEET */

SHEET="&EXL_SHT";    /*for specifying sheets  */

MIXED=YES;

RUN;

%MEND IMPR;

%IMPR(C:\Users\galax_allu\Desktop\Y3DTR.XLS,PURCHASE, PUR);

%IMPR(C:\Users\galax_allu\Desktop\Y3DTR.XLS,SWITCHES, SWT);

Regards,

Allu

[TarunKumar]

Thanx for valuable support.

[SASKiwi]

Here's slightly more automated version where the workbook tab names are used for the SAS dataset names. This means the tabs should be named using SAS naming conventions (A-Z, 0-9, underscores only and no blanks):

%macro Import_Excel_Workbook ( Excel_Libname = INXLSX

                              ,Excel_File =

                              ,SAS_Libname = WORK

                             );

%if &Excel_File ne %then %do;

  libname &excel_libname excel "&Excel_File"; 

%end;

%if (%sysfunc(libref(&excel_libname))) %then

  %put %sysfunc(sysmsg());

%else %do;

%let tmp_sheetlist1 = ;

proc sql noprint ;

select  nliteral(memname)

       ,memname

into  :tmp_sheetlist1 separated by ' '

     ,:dummy

from dictionary.members

where libname = "%upcase(&excel_libname.)" and memname not contains '#'

order by memname

;

quit ;

proc datasets library = &SAS_libname nolist;

  copy in = &excel_libname out = &SAS_libname;

  select &tmp_sheetlist1;

  run;

quit;

  %if &Excel_File ne %then %do;

libname &excel_libname clear; 

  %end;

%end;

%mend Import_Excel_Workbook;