06-16-2016 02:09 AM - edited 06-16-2016 02:53 AM
I need some help about using AMO to open stored process in excel.
The context is : I am trying to open a stored process file in AMO to display 100k+ data records in excel as csv format. The problem is it takes very long to generate outputs due to "proc print function" embeded in "ods csv file=_web rs=none". In order to solve the problem, I have done an experiment:
I do not want to store the csv output anywhere and only looking for an output display in excel when I open stored process in AMO so I have to use _webout (I believe)
06-16-2016 02:47 AM
I guess you meant proc export?
You willl have to present some sample data for testing.
06-16-2016 02:55 AM
thanks for let me know. I have corrected it.
unfortunately I don't have the dataset for you to test out. if you can create a table with 300 columns and total length for all columns=5000 char.
06-16-2016 02:58 AM
You mentioned in your original post that some column names were missing. So the names are important.
06-16-2016 03:04 AM
06-16-2016 03:44 AM
Hmm. That 1021 rings a bell, as it is awfully close to 1024, and may be caused by some implicit limit in the definition of the _webout pseudo-file.
I'd start testing around that limit, to verify it. Maybe then one of the other posters might be able to chime in.
If not, you should put that question to SAS Technical Support.
My modus operandi would always be to keep STP output within the typical limits of web pages, and create web output that can easily be inspected with a simple text editor (or the "View Source" of a web browser). This implies sensible line lengths.
When I have data that extends beyond a table of screen size, I write that to a csv file and provide a link to that file in the web output. Once the web server and browser encounter the MIME type, you won't have problems with line sizes.
06-16-2016 08:09 PM
It seems proc export in stored process sequzze all the columns together in one single cell which may hit the 1024 display limit of excel that might cause truncation...
06-17-2016 01:31 AM
Then try to play around with the DELIMITER= option in the PROC EXPORT statement, You may have to change the DBMS= to DLM for that, but I'd try first with CSV if the DELIMITER= is allowed and has an effect.
06-17-2016 02:43 AM
Then I guess you need to completely redesign your process.
The _webout is actually a destination that assumes HTML text.
proc export creates a CSV stream that does not contain any HTML tags.
AMO expects HTML with a <TABLE> that can then be imported into the table structure of Excel.
So you might end up with writing a data _null_ step that "manually" creates a HTML TABLE and writes it to the _webout file.
Problems like this are the reason I completely avoid AMO and direct interaction with Excel. Instead I create reports that can be viewed via browser, and CSV files for download with links to them in the report. That way my responsibility ends with delivering a correctly structured CSV file with correct data, and Excel can go f... it....
Pardon my frank language, but there's no love lost between myself and Excel.