topic Re: Download data from Stored Process as Excel file in visual analytics in SAS Visual Analytics

Download data from Stored Process as Excel file in visual analytics

Deep04 — Tue, 02 Jan 2024 06:38:59 GMT

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

Re: Download data from Stored Process as Excel file in visual analytics

Patrick — Tue, 02 Jan 2024 12:07:52 GMT

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.

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Tue, 02 Jan 2024 12:18:54 GMT

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.

Re: Download data from Stored Process as Excel file in visual analytics

Patrick — Tue, 02 Jan 2024 13:02:47 GMT

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

Re: Download data from Stored Process as Excel file in visual analytics

Stu_SAS — Tue, 02 Jan 2024 18:42:44 GMT

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.

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Wed, 03 Jan 2024 05:23:44 GMT

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

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Wed, 03 Jan 2024 05:55:43 GMT

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.

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Wed, 03 Jan 2024 06:00:18 GMT

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

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Wed, 03 Jan 2024 06:14:05 GMT

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

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Wed, 03 Jan 2024 06:51:36 GMT

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.

Re: Download data from Stored Process as Excel file in visual analytics

Stu_SAS — Wed, 03 Jan 2024 20:10:40 GMT

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.

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Thu, 04 Jan 2024 09:39:58 GMT

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

Re: Download data from Stored Process as Excel file in visual analytics

Stu_SAS — Thu, 04 Jan 2024 15:31:41 GMT

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.

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Thu, 04 Jan 2024 18:35:19 GMT

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.

Re: Download data from Stored Process as Excel file in visual analytics

Stu_SAS — Thu, 04 Jan 2024 19:03:33 GMT

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;

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Thu, 04 Jan 2024 19:32:55 GMT

Thanks @Stu_SAS . I'll try this code and let you wat happens

Also,my another concern is it's not showing me the html format too because of that user can't see what are they getting in their excel file.

Re: Download data from Stored Process as Excel file in visual analytics

Stu_SAS — Thu, 04 Jan 2024 20:59:58 GMT

If you'd like to still show HTML, remove the "ods html close" line.

Re: Download data from Stored Process as Excel file in visual analytics

Deep04 — Sat, 06 Jan 2024 17:01:53 GMT

Hello, @Stu_SAS I sincerely appreciate your guidance and support, which have enabled me to complete this report.

I had a few inquiries about my work. I have to right-click the _webout.xlsx file in order to get the spreadsheet file. Why isn't it downloading instantly when I click on it?

Furthermore, I have another worry with the report title on my page, which displays the path of the stored process. Can I change it so the user understands it better? Als, by default (maybe), Viya opens the report in a separate tab. Can we do something to open in the report.