import xls with numeric/characters data in SAS

[JohnChen_TW]

Thanks for your explanation.
So, 'libname xlsx' or 'proc copy' can be used with proc report, am I right?

[Kurt_Bremser]

---

@JohnChen_TW wrote:
Thanks for your explanation.
So, 'libname xlsx' or 'proc copy' can be used with proc report, am I right?

---

NO! Proc report creates reports, which are no suited for re-importing. Proc copy copies data. Data can be re-imported and compared.

[JohnChen_TW]

Hi Kurt,

Many thanks for your explanation.

I resolved my question by your and Reeza's reply in another post "How to convert XML files to XLS (Excel 2003)?". All files saved by tagsets excel.xp can be convered to XLS now.

However, the next step after converting XML to XLS, I would like to import to SAS back with code:

Proc Import datafile="xxx.xls" out=test dbms=excel replace; mixed=yes; guessingrows=100; sheet="sheet1"; run;

Per other posts in SAS communities, guessingrows option seems not work for XLS file, so after that I converted XML to XLSX and tried to import it with mixed=yes again. It works, BUT some floats will not displayed what I want. Is there any suggestion to keep the format of floats without another complicated statement? because there're many XLSX files need to be imported. Or, is there suitable code to convert XML to CSV? maybe change the file format num in red as the following code: put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xls"",-4143";

JC

[Kurt_Bremser]

When using TAGSETS.EXCELXP, always use the proper filename extension (.xml).

That prevents mixups as you have just experienced.

Excel usually complains nowadays when the filename extension does not match the file contents.

[ballardw]

Why are you re-importing results from SAS output? Do you mean that you with your system create output with Tagsets. Excelxp and then turn around and import that? Why?

[JohnChen_TW]

ballardw, thanks for your asking.

The reason is that there will two excel files need to be created by two persons from one source dataset which was arranged first, and then outputted by PROC REPORT statement, respectively.

After that I will import these two excel files and verify them to ensure the data was correctly transferred from the source dataset.

So, that is why I need to import excel files back again.

JC