DATA Step, Macro, Functions and more

Program to rename, change and merge table month over month.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Program to rename, change and merge table month over month.

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 FeatureFeature DescriptionRate_201412Rate_201501Rate_201502Rate_201503

Accepted Solutions
Solution
‎01-05-2015 05:46 PM
Super User
Posts: 19,822

Re: Program to rename, change and merge table month over month.

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 Smiley Happy

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.

View solution in original post


All Replies
Solution
‎01-05-2015 05:46 PM
Super User
Posts: 19,822

Re: Program to rename, change and merge table month over month.

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 Smiley Happy

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.

Occasional Contributor
Posts: 19

Re: Program to rename, change and merge table month over month.

Thank you for your response.  I'll try this method and see if it works.

Contributor
Posts: 64

Re: Program to rename, change and merge table month over month.

what is SOC???

Occasional Contributor
Posts: 19

Re: Program to rename, change and merge table month over month.

Posted in reply to Rahul_SAS

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.

Super User
Posts: 11,343

Re: Program to rename, change and merge table month over 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.

Occasional Contributor
Posts: 19

Re: Program to rename, change and merge table month over month.

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.

Super User
Posts: 19,822

Re: Program to rename, change and merge table month over month.

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?

Occasional Contributor
Posts: 19

Re: Program to rename, change and merge table month over month.

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.

Super User
Posts: 11,343

Re: Program to rename, change and merge table month over month.

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 592 views
  • 0 likes
  • 4 in conversation