topic Re: converting excel file to xpt files in SAS Programming

converting excel file to xpt files

Ravindra_ — Wed, 24 Aug 2022 16:39:30 GMT

Can i know how to convert excel files to xpt files. We have got some internal requirement. Thank you. There are few excel files with multiple tabs, and there is a limitation of SAS in our organisation that we will not be able to import excel files, we have only privilege to import csv files. can i know a best solution for this.

Re: converting excel file to xpt files

Sajid01 — Wed, 24 Aug 2022 19:53:07 GMT

Hello
You have few excel files and you cannot import them using SAS.
Then one approach would be save the excel sheets individually as csv and follow your solution.
You have other option of using tools like python or R and convert your files to csv and then follow your solution.

Re: converting excel file to xpt files

Reeza — Wed, 24 Aug 2022 21:10:05 GMT

That's probably a licensing issue but I thought XLSX libname support had moved to Base...you can get VBS scripts to convert workbooks and all sheets to CSV assuming they're well formed. If they're not, you may need Excel macros or some other method to convert those to CSV. Lots of solutions online for this approach..assuming you're on Windows and can run VBS.

https://itecnote.com/tecnote/excel-convert-xls-xlsx-files-all-sheets-to-csv-using-vbscript-delimited-by-semicolons/

Re: converting excel file to xpt files

Ksharp — Thu, 25 Aug 2022 12:21:57 GMT

Or using this free software to convert " GroupDocs.Total Free Apps for Windows "

Re: converting excel file to xpt files

Ravindra_ — Thu, 25 Aug 2022 12:57:54 GMT

As the final xpt files need to be sent to client and they are requesting us to use only SAS for this, is there any solution for this apart from vba script or groupdocs.

Re: converting excel file to xpt files

Sajid01 — Thu, 25 Aug 2022 13:18:21 GMT

Hello @Ravindra_
You have put forth the following two constraints in your posts
(1)There is a limitation of SAS in our organisation that we will not be able to import excel files
(2)As the final xpt files need to be sent to client and they are requesting us to use only SAS for this.

The plausible approach would be to export the excel to csv (preferably from excel itself) and then use SAS to import the data (csv file) and then create a transport file(xpt). This approach will satisfy both of your constraints.
In my view the suggestion by @Reeza needs to be considered. That simplifies the entire process. You may need to convince the decision makers in your company to implement this suggestion.

Re: converting excel file to xpt files

Ksharp — Thu, 25 Aug 2022 13:31:09 GMT

No. As Reeza said, change your excel file into csv file by my suggested software or Reeza's VBA .
After that import these csv file via PROC IMPORT or Data Step, and you can change sas dataset into XPT fille by
xport engine of libname .

Re: converting excel file to xpt files

Ksharp — Thu, 25 Aug 2022 13:36:29 GMT

Oh. you could issue OS command to execute Reeza's VBA in SAS .

Like:

x 'your os command here to execute vba' ;

filename x pipe 'your os command here to execute vba';

data _null_;

call system('your os command here to execute vba');

run;

Re: converting excel file to xpt files

Ravindra_ — Thu, 25 Aug 2022 13:39:32 GMT

VBA is which i am not aware of, i dont think we are allowed to use a third party software to convert excel to csv. Is there a thread available in the community to see any macro been suggested for this use to covert 165 excel files to csv.

Re: converting excel file to xpt files

Ravindra_ — Thu, 25 Aug 2022 13:46:15 GMT

I will try this and let you know

Re: converting excel file to xpt files

Reeza — Thu, 25 Aug 2022 15:18:43 GMT

Well, you can have SAS generate and run the VBS if really necessary.