Download data from Stored Process as Excel file in visual analytics

[Deep04]

Hello Friends,

          I made a stored process that produced an excel file, but now I want to use SAS Visual Analytics to take that stored process and create a report so that other users can download the excel file that best suits their needs in their environment.

I used below code first in my code to generate excel file of stored process.

%global wantreg _odsoptions _odsdest
_odsstyle _odsstylesheet ;
*ProcessBody;
%let _odsdest=excel;
%let _odsstyle=excel;
%let _odsstylesheet=;
%let _odsoptions = GTITLE ;

         so far I imported the spk version of stored process in SAS Environment Manager. but I can't see the same result as I seen in Enterprise guide.

          can anyone please help me with it??

[Patrick]

May be I misunderstand something but isn't this quite a different scenario? 

Wouldn't you in such a case load a table into Lasr (or CAS in Viya) and create a VA report that users then can export as Excel? And it's then the VA report with some filters and the like that lets users customize before they export to Excel.

[Deep04]

Actually I created a stored process in sas enterprise guide that gave me reports in HTML format then I import that stored process in SAS Environment Manager. After importing I opened it in SAS Studio which help me edit the code and the task code of the stored process is in xml language(Attached). But now I want that report in XLSX format so the other user can download the report using sas visual analytics.

[Patrick]

@Deep04 Sorry, but I still miss how you envisage the integration into VA. But then reporting is not my home turf so eventually someone else with more experience in this area will get it and can guide you.

[Stu_SAS]

Hey @Deep04! STPs and Viya Jobs are very similar, but you'll need to make some changes to download data as an Excel file. I assume you're looking to have this available in Visual Analytics for convenience or as a part of another report. 

To write a file to an Excel file that will download to the user's desktop, there are a few things you need to do to your code. The best way to explain this is through an example. Here is code below that will send the results of PROC PRINT to an Excel file that downloads in your browser.

/* Write an Excel file to the job using the filesrvc access method. The job will handle the rest. */
filename excelout filesrvc 
	parenturi="&SYS_JES_JOB_URI" /* URI of the job that we're currently running */
	name='_webout.xlsx' /* You must write to _webout.extension. The filename is controlled in contentdisp. */
	contenttype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' /* Content type */
	contentdisp='attachment; filename="my_excel_file.xlsx"'; /* This is an attachment called my_excel_file.xlsx */
;

ods html close;
ods tagsets.ExcelXP file=excelout style=htmlblue;

proc print data=sashelp.cars;
run;

Use the following parameters for your job:

It's very similar to what you did with STPs, but a little more streamlined. Mostly everything you need to do is done within a filename statement with the filesrvc access method. Many of the macro variables and things you used to set are no longer needed, but many of the familiar names you used are still there (like _webout).

Now if you want to get really advanced, you can use Data Driven Content and pass things from Visual Analytics into the job. That's a pretty complex topic but if you want to learn how to do this, check out the Advanced Scenario Topics section of my paper Modernizing Scenario Analysis with SAS Viya and SAS Viusal Analytics.

[Deep04]

Thanks @Stu_SAS for your help. I'll try this code in mine and let you know.

[Deep04]

Hey @Stu_SAS. I ran the code that you suggest but it got errors (snapshot attached). I also want that user can see report in HTML format before they download in excel. I could see that in html format prior to running the code, but it's not visible anymore.

[Deep04]

additionally, I forget to tell you that I can't open generated excel file.

[Deep04]

Errors which I got in my log -
ERROR: No logical assign for filename _WEBOUT.
WARNING: No body file. HTML output will not be created.

[Deep04]

hey @Stu_SAS as you see in snapshot I attached the file is generated but there are no data in it.

I don't understand why this happening also the log showing no errors.

[Stu_SAS]

It looks like your macro variable for &condition is not resolving and is returning a blank date, resulting in no rows being selected. You may need to check your input form to make sure a date is being passed through correctly. That may be why the downloaded Excel file is not valid. Try hard-coding a valid date that returns data to test that the download works as expected.

[Deep04]

Hello, @Stu_SAS I appreciate your response. I can tell that my macro variable is not resolving based on the prior log. However, even though the macro variable is now resolved, I am still not receiving any data in HTML or Excel format. For your understanding, I'm sharing the pdf of log, and there aren't any errors in it.

Additionally, Could you please explain me what is the Associate a Forms(snapshot attached) in short??

[Stu_SAS]

I do not see any issues within your code. Can you try running the code and job parameter settings that I have posted and see if it works? You do not need to use a form for it and it will run as-is. That can help diagnose if there's something going on with your code or something going on with your browser.

[Deep04]

Thanks for your prompt reply @Stu_SAS . I am running the code by your given setting (pdf attached). but still getting no output of it. I'm not sure where I'm making mistakes in it.

[Stu_SAS · Accepted solution]

Ah I see - thank you! That narrows it down! Try using this ODS statement instead:

ods excel file=excelout style=htmlblue;

Updated code:

/* Write an Excel file to the job using the filesrvc access method. The job will handle the rest. */
filename excelout filesrvc 
	parenturi="&SYS_JES_JOB_URI" /* URI of the job that we're currently running */
	name='_webout.xlsx' /* You must write to _webout.extension. The filename is controlled in contentdisp. */
	contenttype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' /* Content type */
	contentdisp='attachment; filename="my_excel_file.xlsx"'; /* This is an attachment called my_excel_file.xlsx */
;

ods html close;
ods excel file=excelout style=htmlblue;

proc print data=sashelp.cars;
run;