topic Re: Importing Excel Tabs to Different Datasets in SAS Programming

Importing Excel Tabs to Different Datasets

sg_kr — Wed, 22 Jan 2020 09:05:31 GMT

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

Re: Importing Excel Tabs to Different Datasets

ed_sas_member — Wed, 22 Jan 2020 09:34:53 GMT

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

Re: Importing Excel Tabs to Different Datasets

sg_kr — Wed, 22 Jan 2020 16:49:33 GMT

Hi,

thanks for your response.

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

Re: Importing Excel Tabs to Different Datasets

ballardw — Wed, 22 Jan 2020 20:37:05 GMT

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

Re: Importing Excel Tabs to Different Datasets

Tom — Wed, 22 Jan 2020 21:53:47 GMT

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;

Re: Importing Excel Tabs to Different Datasets

sg_kr — Thu, 23 Jan 2020 15:28:23 GMT

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