Hello,
I am fairly new to SAS. I created a table to give me all billing rates for various products or features that I am looking at. I need to run this table once a month to compare the billing rate for the products and features month over month. I would need a program or DATA Step to execute the table and compare the list month over month. Every month I will need to highlight any new or deleted products and features. I will also need to highlight any pricing changes for existing products and features. My Master Table will need to contain the billing rates for all months going forward. I will need to add a new column every month to show the billing rate for that month and if there were any changes. The header will also have to be insterted automaticall with the current month. I will also need to create a separate monthly report with only the current month and the previous months billing rate along with any changes, additions or deletions. For example, this month I would show the billing rate for December and the billing rate for January only, with a comparison of the 2 months. Next month I would compare January and February. I pasted of an example of my headers below. The number of products or features I will be comparing would exceed 30k records per month.
I am not even sure where to start with this. Any assistance would be greatly appreciated.
SOC | Feature | Feature Description | Rate_201412 | Rate_201501 | Rate_201502 | Rate_201503 |
It's easier to append your data so its in a long format and then have a transpose at some point to get the format you need.
ie
SOC Feature FeatureDescription Month Rate
..............201401 1
201402 1
201403 2
Proc Append and Proc Transpose are worth looking into.
For Proc Append read the documentation, it's fairly self explanatory.
For transpose also read the doc, but the link below is useful as well
SAS Learning Module: How to reshape data long to wide using proc transpose
You haven't specified how you want the changes to be presented so I won't comment on that at the moment.
It's easier to append your data so its in a long format and then have a transpose at some point to get the format you need.
ie
SOC Feature FeatureDescription Month Rate
..............201401 1
201402 1
201403 2
Proc Append and Proc Transpose are worth looking into.
For Proc Append read the documentation, it's fairly self explanatory.
For transpose also read the doc, but the link below is useful as well
SAS Learning Module: How to reshape data long to wide using proc transpose
You haven't specified how you want the changes to be presented so I won't comment on that at the moment.
Thank you for your response. I'll try this method and see if it works.
what is SOC???
Its is simply a product that I am comparing month over month. I am looking at the pricing for these products and I am trying to determine if the rate is changing from month to month.
I would add to Reeza's suggestion that the you store the effective date concerned as a SAS date value (probably assume the rate starts on the first of the month but IF that isn't true than the actual date). That way if you ever get into a case were a rate changes more than one time a month you can handle it as well as the date functions and formats that allow simplified comparisons ( quick: how many days between 1 Jan 2015 and 1 Sep 2015) and the display format options for Nicer presentations than 201401 such as Jan 2014 without having to do a lot of coding or post processing.
Thank you for the response. When you say that I should store the effective date concerned as a SAS date value, how would I do that? Just to give you more info, the report will be run at the beginning of each month. I want to merge the reports from the last month and the current month to deteremine what, if anything, has changed. What I am looking for is a pricing change in an existing product, or if a new product appears on the list that didnt appear there the month before.
Read the docs regarding SAS Date Time values, its a bit long, but worth understanding:
SAS(R) 9.2 Language Reference: Concepts, Second Edition
Do you need to store the changes in the master data or only report it?
I will need to store the changes in my master data. However I only need to report on the current month and the previous month. My master will have all months.
If you have a variable that represents a date of information, effective date or date collected or however you may think of it, in your master data set you can always select subsets of data based on that variable with data set option where
data want;
set have (where = ( '01NOV2014'd le DateOfInformation le '31DEC2014'd);
run;
for example.
You could use the above approach to test what a change to a report would look like for prior data or possibly if their became a request to compare quarters. You can get summaries over many different time periods using a formatted value of the date variable without having to create new variables. Suppose you want to examine the maximum and minimum of the billing rate for each product over time at quarterly intervals:
Proc means data=have max min;
class product DateOfInformation;
var Billingrate;
format DateOfINformation YYQ6.;
run;
Same information for yearly intervals:
Proc means data=have max min;
class product DateOfInformation;
var Billingrate;
format DateOfINformation Year4.;
run;
Or a review of the monthly:
Proc means data=have max min;
class product DateOfInformation;
var Billingrate;
format DateOfINformation YYMM6.;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.