topic Export to XLSX file and add a new column every month in SAS Programming

Export to XLSX file and add a new column every month

Ronein — Mon, 12 Jul 2021 17:11:27 GMT

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

Re: Export to XLSX file and add a new column every month

tarheel13 — Mon, 12 Jul 2021 18:46:26 GMT

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

Re: Export to XLSX file and add a new column every month

Kurt_Bremser — Mon, 12 Jul 2021 18:52:21 GMT

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

Re: Export to XLSX file and add a new column every month

ballardw — Mon, 12 Jul 2021 19:09:46 GMT

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