topic Re: multiple sheets in excel in ODS and Base Reporting

multiple sheets in excel

TarunKumar — Thu, 06 Jun 2013 03:33:17 GMT

Hi,

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

Re: multiple sheets in excel

allurai0412 — Thu, 06 Jun 2013 05:11:30 GMT

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

Re: multiple sheets in excel

TarunKumar — Thu, 06 Jun 2013 10:27:43 GMT

Thanx for valuable support.

Re: multiple sheets in excel

SASKiwi — Thu, 06 Jun 2013 20:37:39 GMT

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;

%mend Import_Excel_Workbook;