Export to XLSX file and add a new column every month

[Ronein]

Hello,

Lets say that every month I need to run SAS code that calculate number of customers in each city.

Let's say that I start run this report today (July 2021) and the data for July 2021 is :

City        Nr_customers2107

London         250

New Castle  300

Machester   180

Then I want to export it into one XLSX file called "Customers_by_month" with one sheet called "Output".

Next month (Aug 2021) I will run the same report and lets say the data will be :

London         220

New Castle  250

Machester   200

Then I want to export this data into same XLSX file and add a new column called "Nr_customers2108"

and so on every month.

What is the way to do it please?

The task is to export to one XLX file (one sheet) and each month export to same XLSA file and add a new column

Thanks

[tarheel13]

I think you need to use macro variables for the months. You can use proc export to send it XLSX.

[Kurt_Bremser]

Use ODS EXCEL and PROC REPORT to create the wide report from the long dataset; use the month variable as ACROSS.

[ballardw]

---

@Kurt_Bremser wrote:

Use ODS EXCEL and PROC REPORT to create the wide report from the long dataset; use the month variable as ACROSS.

---

Which would overwrite the existing report, not add a column.

My approach as well when first reading the request. Then I started thinking about a "partial" input data set that doesn't contain all the data for all the columns as a possible use by the OP. Which would not have all the columns.