Hi ,
I have an excel with multiple number of sheets in that, now i need a macro to import each sheet in excel to each dataset with sheet name.
i cannot give the sheet name manually every time it needs to be dynamic
To copy each sheet from a file named 'physical filename.xlsx' to datasets in the WORK library first make a libref pointing to the XLSX file and then use PROC COPY to copy the datasets that SAS sees in the workbook. Each sheet should be its own dataset.
libname HAVE xlsx 'physical filename.xlsx';
proc copy inlib=HAVE outlib=WORK;
run;
Hi @sg_kr,
Assuming you have the adequate licence, you can use the EXCEL engine in a LIBNAME statement:
LIBNAME mylib XLSX "your_path/you_excel_file.xlsx";
Hi,
thanks for your response.
this is not regarding license issue,i want to import different tabs to different datasets using code
@sg_kr wrote:
Hi,
thanks for your response.
this is not regarding license issue,i want to import different tabs to different datasets using code
The LIBNAME approach that @ed_sas_member demonstrates requires that the SAS/Access Interface to PC Files module be available.
You can see if you have that module installed and ready to go with this code:
Proc product_status; run;
When you run the code then the LOG will contain something like:
For SAS/ACCESS Interface to PC Files ... Custom version information: 9.4_M4
If you do not see the Access Interface to PC Files you can see if you have the product licensed but not installed with
Proc setinit; run;
If the log shows
---SAS/ACCESS Interface to PC Files
then it is licensed but not installed (if not appearing in the Product_status output) and you could reinstall SAS insuring that the module is selected for install.
If you don't have that installed then you will likely not find any automated way to read multiple sheets because the other tools don't have ability to parse out the information from the Excel file directly.
To copy each sheet from a file named 'physical filename.xlsx' to datasets in the WORK library first make a libref pointing to the XLSX file and then use PROC COPY to copy the datasets that SAS sees in the workbook. Each sheet should be its own dataset.
libname HAVE xlsx 'physical filename.xlsx';
proc copy inlib=HAVE outlib=WORK;
run;
This is working but the sheet names with spaces and '-' between them facing naming erros
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: