topic Re: Every month update an excel file with new monthly information in SAS Programming

Every month update an excel file with new monthly information

Ronein — Fri, 15 Dec 2017 08:43:07 GMT

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

Re: Every month update an excel file with new monthly information

PeterClemmensen — Fri, 15 Dec 2017 08:49:19 GMT

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.

Re: Every month update an excel file with new monthly information

Ronein — Fri, 15 Dec 2017 08:56:20 GMT

Let's do it most simple.

The excel DEC input data includes one columnn

Re: Every month update an excel file with new monthly information

Ronein — Fri, 15 Dec 2017 08:58:24 GMT

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

Re: Every month update an excel file with new monthly information

RW9 — Fri, 15 Dec 2017 09:06:58 GMT

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.

Re: Every month update an excel file with new monthly information

ballardw — Fri, 15 Dec 2017 16:14:09 GMT

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

Re: Every month update an excel file with new monthly information

Ronein — Sat, 16 Dec 2017 07:08:31 GMT

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??