topic Re: Merge monthly data - PROC APPEND in SAS Programming

Merge monthly data - PROC APPEND

Giuliano — Wed, 07 Jan 2015 21:55:31 GMT

Hello,

I have created a table which I will need to run on a montly basis to compare pricing changes month over month on products and services, and to detemine when a new product or service is introduced. I will be running my report at the beginning of each month and then performing a PROC APPEND to merge the products and services. The first month I will need all information brought in. After that I just need the updated pricing for that current month and also any new products or services that were introduced. I need to have my read vertically in columns similar to the ones I pasted below. I understand that I will need to Transpose my data afterwards to change the view from horizontal to vertical. The SOC, Feature and Description columns will remain constanst however I will need to add a new column ever month, for ex. Rate_201501, Rate_201502, Rate_201503. every I will have approx 50k record in my table. Most months, the Products and Services will not change, just the rate might change.

I am assuming that I will probably need to insert another column to my current table so that I can insert the Month/Year that I am running the table for. I have no run date field in my current table. When I run multiple tables now, it will list all my results horizontally. I have no way of distinguishing which variables are from which month. My question is, how do I add the date month over month to my current table and have it display as it does in the second table? Maybe I'm going about this wrong but any input would be appreciated.

These are the fields on my current table. Right now my data is displayed horizontally.

SOC

FEATURE_CODE

RATE

SOC_DESCRIPTION

PRODUCT_TYPE

This is how I want my table to look as I am adding the months in. I need to Transpose my data so that it displays vertically in this format. And I need to add a new header each month. Not sure how I can achive this.

SOC

Feature

Feature Description

Rate_201412

Rate_201501

Rate_201502

Rate_201503

Re: Merge monthly data - PROC APPEND

LinusH — Thu, 08 Jan 2015 09:16:56 GMT

Use PROC TRANSPOSE or a data step to transpose your data. There are tons of examples available.

But, why? Your target table will be more difficult and less flexible to build reports on. Let the reporting tolls do the layout for you. Keep the period and the rate in separate columns, much easier to maintain.

Re: Merge monthly data - PROC APPEND

RW9 — Thu, 08 Jan 2015 09:34:34 GMT

I concur with LinusH, if you have that many records then you are going to end up with vast amounts of columns. I would recommend keeping it normalized unless there is actually a requirement. AS for the date, I couldn't see anything in the horizontal structure you showed to have date, so how is that arrived at. Paste some actual data and what you are trying to achieve.

Re: Merge monthly data - PROC APPEND

Giuliano — Thu, 08 Jan 2015 13:43:12 GMT

Ok, so you are saying that I should use PROC REPORT after I do my PROC APPEND to display the results needed? And forget about about using PROC TRANPOSE?

Re: Merge monthly data - PROC APPEND

Reeza — Thu, 08 Jan 2015 13:51:19 GMT

They're saying the store the data in a long format, as mentioned here:

And only use your wide format to create your reports. It's easier than adding columns every time your table rather than just appending rows each time.

Re: Merge monthly data - PROC APPEND

Giuliano — Thu, 08 Jan 2015 14:01:24 GMT

Ok perfect, thank you Reeza.

Re: Merge monthly data - PROC APPEND

DBailey — Thu, 08 Jan 2015 14:03:29 GMT

A more normal data structure might look something like this (presuming SOC is the unique key):

SOC_Dim

SOC

SOC_Description

Feature_Code

Product_Type

SOC_Fact

SOC

Business_Date

Rate

You would need to insert new products records into SOC_DIM each month and all records into the fact table:

proc sql;

create table work.soc_new as

select

t1.SOC

t1.SOC_Description

t1.Feature_Code

t1.Product_Type

from

NewData t1

left outer join work.soc_dim t2

on t1.soc=t2.soc

where

t2.soc is null;

insert into soc_dim (

SOC

SOC_Description

Feature_Code

Product_Type)

select

SOC

SOC_Description

Feature_Code

Product_Type

from work.soc_new;

insert into SOC_FACT (

soc,

business_Date,

rate)

select

soc,

business_date, /*not sure where the date actually comes from*/

rate

from

work.new_data;

quit;

Then, it's a fairly straightforward question to use in queries/views:

proc sql;

select

t1.*,

t2.rate as Rate_20141231

from

soc_dim t1

left outer join (select * from soc_dim where business_Date='31dec2014'd) t2

on t1.soc=t2.soc

;

quit;