Hi i have a excel file which has multiple sheets with name Jan, Feb, March, April, ....Dec. I want to load this data for further use using macro.
Thankyou
If you are using SAS 9.4 (since I think TSM2?) then just use the XLSX engine on a libname statement and you can treat the sheets as if they were datasets. This does not require Excel and so works on Unix in addition to Windows, but I think it does require that you have SAS/Access to PC files licensed.
I find it is useful to just use PROC COPY to copy all of the sheets into WORK datasets and then I can use them from there.
libname in xlsx 'myfilename.xlsx';
proc copy inlib=in outlib=work;
run;
First rule of programming club, macro is never needed. What version of SAS/Excel are you using? If your using 9.4 then the simplest way to do it is to use the libname excel method:
libname myxlsx excel "<path to file>\<filename>.xlsx"; ... libname myxlsx clear;
The libname excel will open an XLSX file, and create a dataset for each sheet it find which is compatible. You can then use simple statements like:
libname myxlsx excel "<path to file>\<filename>.xlsx"; data want;
set myxlsx.'Sheet 1'n;
run; libname myxlsx clear;
If you are using SAS 9.4 (since I think TSM2?) then just use the XLSX engine on a libname statement and you can treat the sheets as if they were datasets. This does not require Excel and so works on Unix in addition to Windows, but I think it does require that you have SAS/Access to PC files licensed.
I find it is useful to just use PROC COPY to copy all of the sheets into WORK datasets and then I can use them from there.
libname in xlsx 'myfilename.xlsx';
proc copy inlib=in outlib=work;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.