BookmarkSubscribeRSS Feed
Deep04
Obsidian | Level 7

Can someone please explain me the following code,

 

filename excelout filesrvc
parenturi="&SYS_JES_JOB_URI" 
name='_webout.xlsx' 
contenttype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 
contentdisp='attachment; filename="my_excel_file.xlsx"'; 
;
ods excel file=excelout style=htmlblue
options(sheet_name="Receipt Data" embedded_titles='yes');

2 REPLIES 2
KeithM
SAS Employee

1. from the code, it is unknow where your Excel file is located so in order for me to get it to work, I needed to point to an Excel file in metadata.  I went to SAS Drive and looked for an existing spreadsheet.  Then looked at the properties to get the uri.

 

filename excelout filesrvc '/files/files/5bb9d9bd-1b4f-4d04-8fb2-4e0992d1aaf7'
parenturi="&SYS_JES_JOB_URI"
name='_webout.xlsx'
contenttype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
contentdisp='attachment; filename="my_excel_file.xlsx"';
;
ods excel file=excelout style=htmlblue
options(sheet_name="Receipt Data" embedded_titles='yes');

 

2. I created a Job in ..../SASJobExecution app.  When I run the job, I get:

KeithM_0-1709048194370.png

3. When you click on _webout.xlsx, you are prompted to download the file 

KeithM_1-1709048378054.png

I suspect the _webout.html is in reference to the parenturi option specified.  I suspect if you were linking to this code from another web page then that would be similar to a back button.

 

In regards to a definition of everything in the code, I reached out to ChatGPT for a decent definition:

FROM CHATGPT:

 
 

The provided SAS ODS (Output Delivery System) code is used to generate an Excel file with the name "my_excel_file.xlsx" and a sheet named "Receipt Data." Let's break down the code:

  1. filename excelout: Specifies the filename and file location for the Excel output.

  2. filesrvc: Specifies that the file is to be stored on the server.

  3. parenturi="&SYS_JES_JOB_URI": Specifies the parent URI for the file. &SYS_JES_JOB_URI is a system macro variable that provides the URI of the job.

  4. name='_webout.xlsx': Specifies the name of the Excel file to be created, which is '_webout.xlsx'.

  5. contenttype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet': Specifies the content type for

the HTTP response header. In this case, it indicates that the content is an Excel file in the Office Open XML format.

  1. contentdisp='attachment; filename="my_excel_file.xlsx"';: Specifies the content disposition for the HTTP response header. It sets the disposition type to 'attachment', indicating that the content should be treated as an attachment. The filename parameter is set to "my_excel_file.xlsx."

  2. ods excel file=excelout: Opens the ODS Excel destination and directs the output to the file defined by the excelout filename statement.

  3. style=htmlblue: Specifies the style for the Excel output. In this case, it uses the 'htmlblue' style.

  4. options(sheet_name="Receipt Data" embedded_titles='yes');: Specifies additional options for the Excel output. It sets the sheet name to "Receipt Data" and enables embedded titles in the Excel sheet.

In summary, this SAS code generates an Excel file named "my_excel_file.xlsx" with a sheet named "Receipt Data." The Excel file is stored on the server, and the HTTP response header indicates that it should be treated as an attachment with the specified filename. The style 'htmlblue' is applied to the Excel output, and embedded titles are enabled in the sheet.

 

 

 

Deep04
Obsidian | Level 7

Thanks @KeithM  for your great explanation. 

but there are bunch of question I have.

1. Can we change the appearance of _webout.html and _webout.xlsx to like download excel here so the user can understand click there to download excel.

2. Is &SYS_JES_JOB_URI only resolves in SASJobExecution ?

 

Also I didn't get your last point about _webout.html , can you please narrow it down?

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 313 views
  • 1 like
  • 2 in conversation