The SAS Output Delivery System and reporting techniques

multiple sheets in excel

Reply
Frequent Contributor
Posts: 77

multiple sheets in excel


Hi,

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

Frequent Contributor
Posts: 97

Re: multiple sheets in excel

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

Frequent Contributor
Posts: 77

Re: multiple sheets in excel

Thanx for valuable support.

Super User
Posts: 3,238

Re: multiple sheets in excel

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;

Ask a Question
Discussion stats
  • 3 replies
  • 470 views
  • 2 likes
  • 3 in conversation