AMO open stored process using proc import caused missing headers

[gyambqt]

Hello Experts,

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:

1. use proc import data=xxx outfile=_webout replace dbms=csv(or tab); run; to avoid the "proc print function" (to replace ODS)
   • problem encountered: some columns names are missing (it works fine in a normal SAS EG run with proc import)

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)

my questions:

1. is there anyway to surpress the proc print in ods statement?
2. if I use proc export why would it results missing columns name and any work around?

Thanks

[Kurt_Bremser]

I guess you meant proc export?

You willl have to present some sample data for testing.

[gyambqt]

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.

[Kurt_Bremser]

You mentioned in your original post that some column names were missing. So the names are important.

[gyambqt]

They are just random names, they have like 100 columns and form 5321 characters in total (when you concatenate them together). all the columns after 1021 characters has been truncated(missing columns).

[Kurt_Bremser]

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.

[gyambqt]

Hi Expert,

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...

[Kurt_Bremser]

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.

[gyambqt]

Hi I have tried that and it didn't work.z

[Kurt_Bremser]

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.