SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

AMO open stored process using proc import caused missing headers

Reply
Regular Contributor
Posts: 152

AMO open stored process using proc import caused missing headers

[ Edited ]

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

Esteemed Advisor
Posts: 6,646

Re: AMO open stored process using proc import caused missing headers

I guess you meant proc export?

 

You willl have to present some sample data for testing.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 152

Re: AMO open stored process using proc import caused missing headers

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.

Esteemed Advisor
Posts: 6,646

Re: AMO open stored process using proc import caused missing headers

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 152

Re: AMO open stored process using proc import caused missing headers

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).
Esteemed Advisor
Posts: 6,646

Re: AMO open stored process using proc import caused missing headers

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 152

Re: AMO open stored process using proc import caused missing headers

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

Esteemed Advisor
Posts: 6,646

Re: AMO open stored process using proc import caused missing headers

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 152

Re: AMO open stored process using proc import caused missing headers

Hi I have tried that and it didn't work.z
Esteemed Advisor
Posts: 6,646

Re: AMO open stored process using proc import caused missing headers

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 9 replies
  • 379 views
  • 0 likes
  • 2 in conversation