Trying to add a description sheet to an xlsx report

Question

Currently, I have a data dump report using proc export that I run every so often and all the aggregations are written onto the report e-mail via put() statements.

so pretty much

x.aggre_0 looks like

   total 1 total 2 total 3
    123      321    567

Current datastep looks like this:

 data x.aggr_1;
 set x.aggre_0;
 call symput("e-mailcontents",
 cat('Summary #1:',total 1)
 cat('Summary #2:',total 2)
 cat('Summary #3:', total 3))

and I just put(&e-mailcontents) onto my e-mail sas file.

That's the jist of what the process is like now.

Got a new request to add a summary tab to the report rather than inserting all aggregations onto the e-mail. However, I'm not sure as to how i'm going to go about appending text onto a xlsx using proc export. ODS Excel and xlsx engines aren't an option due to constraints beyond my control.

I have my aggregations saved into x.aggre_0, but I'm not sure as to how I'm going to place it in another sheet in the same report.

I was thinking of something along the lines of just creating a new table with one column and just having it show line by line like this, but I have no clue how.

   column_name
 1 Summary #1: 123
 2 Summary #2: 321
 3 Summary #3: 567

You are using PROC EXPORT to do what exactly? How is the result of PROC EXPORT included into the email message now? — Tom
Hey, sorry for the lack of clarification. I'm currently using PROC Export to export the report file,which is attached to an e-mail that I send out. Within the body of the e-mail is an aggregation that I do in a macro within the original stored procedure, which is inserted into the e-mail body using the macro variable &email_content. — dw0000
Export to what format? Assuming the first export is creating something that can actually store another tab, like an Excel workbook, it sounds like all you are asked to do is add another Export step to add another tab to whatever it was the first export created. Otherwise if you cannot add another tab to it then you will need to create another file and attach both files to the email instead. — Tom
The final output is an .xlsx file. I know I can utilize proc export to create additional sheets. However, the issue is more of being able to append regular text onto a spreadsheet without the use of ODS Excel. Since it seems like proc export only works with sas7bdat files, i'm not sure how i'd go about adding things like for example.. Total Sales: $1,000,000.. Count of Rows: 1236.. etc right onto the secondary tab of the excel report. — dw0000

John John · Accepted Answer · 2020-02-05T16:13:20

It sounds like you are trying to create a dataset that has your current summary data transposed. You can look into proc transpose, but for your very simple situation, I would probably just do something like this:

data summaryinfo;
    set x.aggre_0;
    summaryinfo = cat('Summary #1:',total 1); output;
    summaryinfo = cat('Summary #2:',total 2); output;
    summaryinfo = cat('Summary #3:',total 3); output;
    keep summaryinfo;
run;

proc export data = summaryinfo;
    outfile = "your-file.xlsx"
    dbms = excel;
    sheet = "Summary Info";
run;

Trying to add a description sheet to an xlsx report

1 Answers