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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.