topic Re: Dataflux - Add a Grand total row to an excel output file in SAS Data Management

Dataflux - Add a Grand total row to an excel output file

beamer108 — Fri, 19 Mar 2021 17:26:03 GMT

I have a datajob that writes output to an excel file. It shows multiple rows which include fields like total_sales, total_income etc. At the end of my dj I have data going to an "text file output" node which then feeds into a "Data Target (insert)" node. Is it possible to create a final summary row to the output that would give me a grand total for each of these columns?

Re: Dataflux - Add a Grand total row to an excel output file

VincentRejany — Mon, 22 Mar 2021 08:46:43 GMT

You can either add a calculated field

an "expression node" with the following code

pre processing

real myTotal

expression

myTotal = toreal(total_sales) + toreal(total_income)

Re: Dataflux - Add a Grand total row to an excel output file

beamer108 — Mon, 22 Mar 2021 12:19:18 GMT

Im actually looking to add a row not a column. So it would look something like this: (row in red is what I am looking to add)

Re: Dataflux - Add a Grand total row to an excel output file

VincentRejany — Mon, 22 Mar 2021 14:54:56 GMT

for adding a final line with a grand total, do the following

//pre processing expression

hidden real hgrandTotalSales

hidden real hgrandTotalIncome

hgrandTotalSales = 0

hgrandTotalIncome = 0

//expression

hgrandTotalSales = hgrandTotalSales+ toreal(total_sales)

hgrandTotalIncome = hgrandTotalIncome+ toreal(total_income)

//post processing expression

service_from_date = null

total_sales = hgrandTotalSales

total_income = hgrandTotalIncome

pushrow()

Re: Dataflux - Add a Grand total row to an excel output file

beamer108 — Mon, 22 Mar 2021 15:44:17 GMT

Thank you SOOO much! This is amazing. I had almost convinced myself that it wasn't possible. Thank you!