BookmarkSubscribeRSS Feed
JohnChen_TW
Quartz | Level 8
Thanks for your explanation.
So, 'libname xlsx' or 'proc copy' can be used with proc report, am I right?
Kurt_Bremser
Super User

@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
Quartz | Level 8

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
Super User

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
Super User

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 1527 views
  • 5 likes
  • 5 in conversation