01-07-2015 04:55 PM
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.
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.
01-08-2015 08:51 AM
01-08-2015 04:16 AM
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.
01-08-2015 08:51 AM
01-08-2015 04:34 AM
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.
01-08-2015 09:03 AM
A more normal data structure might look something like this (presuming SOC is the unique key):
You would need to insert new products records into SOC_DIM each month and all records into the fact table:
create table work.soc_new as
left outer join work.soc_dim t2
t2.soc is null;
insert into soc_dim (
insert into SOC_FACT (
business_date, /*not sure where the date actually comes from*/
Then, it's a fairly straightforward question to use in queries/views:
t2.rate as Rate_20141231
left outer join (select * from soc_dim where business_Date='31dec2014'd) t2
Need further help from the community? Please ask a new question.