Hi,
I have multiple excel sheet. i want to import all sheets with the help Of macro.
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
Thanx for valuable support.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: