BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

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
Meteorite | Level 14

Let's do it most simple.

The excel DEC input data includes one columnn

Ronein
Meteorite | Level 14

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Meteorite | Level 14

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2430 views
  • 4 likes
  • 4 in conversation