04-27-2015 06:36 PM
The joke is on me but I need to import files that were exported in SAS 9.2 in ODS ExcelXP tagset as XML-
04-27-2015 08:06 PM
Do the files have formatting/multiple tables per sheet? Or are they simple proc prints?
You might need to post a sample. If you have a variety of files that makes it harder as well.
Several options are: DDE read in, Convert file to CSV/Text and read in, or Parse the XML. All will have gotcha's, but without knowing more it's hard to say.
04-28-2015 06:29 PM
What gets generated is a multi-tabbed document with six different sheets in it. It is hard to post as it has a lot of HIPAA protected data and internal data reserved only for the medical centers. By trying to mask everything takes a bit, but I will see what I can do.
Thanks Cynthia. I will take a look.
I have the code that generates the file but I don't have the data as it grabs information from the server and then exports the files.
Long story to why I don't have the data previous to April. I do have a program that retro-fill in the data but it takes a long time actuall to process each day.
I did try resaving as an Excel file which failed. Saving as a CSV is not possible.
04-27-2015 08:17 PM
Theoretically, you might be able to use an XML MAP to import the SpreadsheetML 2003 XML back into SAS. This type of "round tripping" was described in this paper: http://support.sas.com/rnd/papers/sugi29/ExcelXML.pdf for a previous version of SAS and the TAGSETS.EXCELXP. I am not sure whether it will still work or not.
04-28-2015 05:08 AM
And you don't have either
A) The code used to generate the file
B) The data used to generate the file
My suggestion would be to open the file in Excel, save as CSV, then write a datastep to import the CSV data back again.
04-29-2015 11:44 AM
Yes saving it to a xls format in itself and doing a proc import works.
The issue is that I have some 2000 files!
RW9-Sorry if i was not more articulate-
What I had tried to do, and knew that it would fail, was to simply copy the file and rename the extension to xls.
CVS does not work in this case as the files are multi-tabbed.
In short what I was attempting to do was to find a faster way to import the data as many times I can build a pipe to import the file names and then a macro to import the data from each file, reassemble all the data. Usually it works really fast.
I have another method to get at the same data by pulling directly from the database. Unfortunately it is slow moving and will take about 2 days to process it all.
Cynthia-the XML mapping works insofar as it imports the different worksheets. However I am not sure if there is a parameter to set the row to start importing the data. Also that the some of the worksheets have names greater than 32 characters presents an issue as it won't import those sheets.
Thanks for all your assistance on this. I consider this issue closed.
04-29-2015 11:58 AM
Just to help on the file formats. Saveas can easily be automated using VBA. Here is a the first link from Google:
Shows a simple do loop over sheets to save each to files. You can then also wrap around that a file open/close for each file in a directory. A bit of code in Excel and you could convert a whole set of Excel files to a series of csv files in no time. If the data is all the same, you could just keep streaming it out to one CSV.
As for the 32 characters, you can use: options validavarname=any;
You could also dump the data directly from the database, maybe quicker.
Good luck with it.