Importing Excel Tabs to Different Datasets

[sg_kr]

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

[ed_sas_member]

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";

[sg_kr]

Hi,

thanks for your response.

this is not regarding license issue,i want to import different tabs to different datasets using code

[ballardw]

---

@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.

[Tom · Accepted solution]

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;

[sg_kr]

This is working but the sheet names with spaces and '-' between them facing naming erros