Merge monthly data - PROC APPEND

[Giuliano]

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

[LinusH]

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.

[Giuliano]

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?

[Reeza · Accepted solution]

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.

[Giuliano]

Ok perfect, thank you Reeza.

[RW9]

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.

[DBailey]

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;