Every month update an excel file with new monthly information

[Ronein]

Dear friends

I have the following request.

I have an excel file that conatins monthly outputs in same sheet (output from Jan2017 until Nov2017)

This file was creates manually by copy and paste SAS output from input excel file that I receive every month.

The challenge now is to perform automatics process that includes following steps:

Import monthly excel raw data into SAS

Create analysis on this file with summary statistics output

Import  into  SAS the cumulative output excel file that contains information from JAN2017 until Previous month 

Add the new summary statistics information to  cumulative output excel file

Export  the updated  cumulative output  to excel file 

I will explain it now in more details:

Step1-

Every month I will receive an excel file(let’s call it input file) with data on it.

I need to import the excel file into sas data set and perform calculations in order to get output file.

This output file information includes for example 1 column.

Let’s say that the last month of analysis was DEC2017

Step2-

There is existing cumulative periods excel file that includes ouputs from Jan2017 until  NOV2017

I need to import this file and add a column with results of  DEC2017

Step3-

Export  the updated cumulative periods excel file that includes now months JAN2017-DEC2017

May someone send an easy example  code  how to do it

[PeterClemmensen]

2 things:

- So your attatched Excel file is your desired output? What does the input data look like? Provide a sample.

- It is rarely a good idea to have dynamic columns, ie. columns that represent eg time. Instead, transpose your data so you have four columns: Month, Total sales, Total Costs and Net.

[Ronein]

Let's do it most simple.

The excel DEC input data includes one columnn

[Ronein]

The question is :

How to import SAS results into existing excel file

How to tell SAS to paste the export in a new column and not override the  existing columns for previous months

[RW9]

Well first off, let me start by saying Excel is the corenerstone of every bad process in existence.  If you insist on using that as a data medium be prepared to debug and alter your code every time you run the code.  You will find this out when you start, there are so many "features" that will trip up your code, or user changes that will mess with the code.  But anyways, you wont listen, but you will find out.

The easiest way to do what you say below is to use XLSX format and libname xlsx:

https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

With this you can proc copy data in and out, i.e. take all data from the xlsx, do your code, then write the data back again.  Very simple.  Any other solution will end up being far more work than this, trust me.

[Ronein]

Thank you so so much

It is a great solution I think.

Just to be sure about the step.

step1- Import exiting excel output file (Let's  call it  data set A)

Step2- Do your code on current month in order to produce new summary statsitics for current month (Let's  call it  data set b)

Step3- Merge  data set A and B

IS it correct??

[ballardw]

---

@Ronein wrote:

Dear friends

I have the following request.

I have an excel file that conatins monthly outputs in same sheet (output from Jan2017 until Nov2017)

This file was creates manually by copy and paste SAS output from input excel file that I receive every month.

The challenge now is to perform automatics process that includes following steps:

Import monthly excel raw data into SAS

Create analysis on this file with summary statistics output

Import  into  SAS the cumulative output excel file that contains information from JAN2017 until Previous month 

Add the new summary statistics information to  cumulative output excel file

Export  the updated  cumulative output  to excel file 

---

The line I highlighted in red is really a major flaw in the process. Create output and then read it back in? Is there something being done to that data manually. If so you don't say. Keep the original data in SAS, append the new data in an appropriate format, and recreate the output - all of if.

The major bit of reimporting is that I have seen too many Excel files where people start putting formula and undocumented manual changes. If you have actual manual changes that need to be done then formally establish the process and UPDATE existing data.

A single data set with all of the data would then make it very easy for the time someone wants to look at a three year set, Or some other interval such as July to June of the next year.