topic Re: SAS Viya Job: Excel download in Developers

SAS Viya Job: Excel download

jins — Fri, 21 Oct 2022 19:28:57 GMT

HI,
If I run the code below, why is there _webout.html and _webout.xls links. I just want the ok.xlsx 
file to download. Is there a way to do it?
 
* Create the Excel XML output and associate with the job;
filename xmlfile filesrvc parenturi="&SYS_JES_JOB_URI"
name='_webout.xls'
contenttype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
contentdisp="attachment; filename=ok.xlsx";

ods excel file=xmlfile options(sheet_name="chk" frozen_headers='1');

  proc print data=sashelp.class noobs;
    title 'test3 ods excel mimetype=application/vnd.ms-excel';
  run; 
ods _all_ close;

Re: SAS Viya Job: Excel download

AllanBowe — Fri, 21 Oct 2022 19:45:20 GMT

There is an excel download option in Data Controller for SAS - here is the SAS code behind the download service: https://code.datacontroller.io/getrawdata_8sas.html

It uses this macro directly: https://core.sasjs.io/mp__streamfile_8sas.html

Here's an extract:

    filename &outref filesrvc parenturi="&SYS_JES_JOB_URI" name='_webout.xls'
      contenttype='application/vnd.ms-excel'
      contentdisp="attachment; filename=&outname";

This works fine for DC customers. How exactly are you "running" the code? Are you deploying it as a Job and opening the URL using the JES Web App with the appropriate _PROGRAM parameter?

Or calling via the JES REST APIs?

Re: SAS Viya Job: Excel download

jins — Fri, 21 Oct 2022 20:20:37 GMT

deploying as a job and opening the URL using the JES Web App. What should be the _PROGRAM parameter in this case? It was a bit confusing in SAS docs...

Re: SAS Viya Job: Excel download

AllanBowe — Fri, 21 Oct 2022 20:37:30 GMT

The `_program=/should/be/the/path/to/your/job/in/sas/drive` WITHOUT a .sas extension

Re: SAS Viya Job: Excel download

jins — Fri, 21 Oct 2022 20:46:05 GMT

The parameter _output_type =none seems to suppress the creation of those links. Do you concur on this?

Re: SAS Viya Job: Excel download

jins — Fri, 28 Oct 2022 15:16:15 GMT

HI Allan,

I migrated the stored process from 9.4 to Viya 3.5. I've the options of either creating html, xlsx and pdf as a drop down. Unable to figure out as to how to set the options for the automatic download for each of the reports based on selection. If I select _output_type='none' then I get the following error:

ERROR: No logical assign for filename _WEBOUT.

if I set _output_type='ods_html5' or html, then I get the links of _webout.html and _webout.xls instead of automatic download for the selected report type. Any thoughts on this?

Re: SAS Viya Job: Excel download

AllanBowe — Fri, 28 Oct 2022 15:34:26 GMT

Hi jins - am happy to try and help but it may be easier to do this through a screen share. Feel free to book a convenient slot with me using this link: https://4gl.uk/book

We can then report back with the solution should we find one.

Re: SAS Viya Job: Excel download

jins — Mon, 31 Oct 2022 15:31:16 GMT

HI Allan,

I can set that up later with an example but had a question if the following can be streamed if i am using tagset.tableeditor? (i've set _output_type as none in parameter)

%if &outputtp=HTML %then %do;
* Create the Excel XML output and associate with the job;
	filename htmlfile filesrvc parenturi="&SYS_JES_JOB_URI"
	name='_webout.json'
	contenttype='text/html'
	contentdisp="attachment; filename=ok.html";

	*ods html file=htmlfile;
    ods tagsets.tableeditor file='htmlfile'
    style=styles.MyStyle
    /*options(autofilter="YES" 
 	        autofilter_table="1" 
            autofilter_width="6em" 
 	        autofilter_endcol= "7" 
            frozen_headers="YES" 
            frozen_rowheaders="YES" 
            )*/ ; 
	
	proc print data=sashelp.class noobs;
	   title 'Test ODS HTML';
	run; 
    ods tagsets.tableeditor close;
    ods listing;
%end;

Re: SAS Viya Job: Excel download

AllanBowe — Mon, 31 Oct 2022 15:36:35 GMT

You can stream ANYTHING you like! Generally I'd look to create the file, then just stream it as it is, rather than using ODS but it should still work.

I had a realisation about this - are you creating the Jobs manually (not using SASjs)?

If so, have you set the option to remove the automatic JES macros? `_addjesbeginendmacros=false`

These work similarly to the `stpbegin` and `stpend` macros in SAS EBI, and can affect the usability of the _webout destination.

Re: SAS Viya Job: Excel download

jins — Wed, 02 Nov 2022 13:58:37 GMT

HI,

Say I created this file test.html in a location as follows....

ods tagsets.Tableeditor file='/xxxx/xxx/xx/test.html'

How do I call this created file (test.html) to download using the filename filesrvc statements?

Thanks for your patience!