BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Giuliano
Fluorite | Level 6

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
Reeza
Super User

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.

Giuliano
Fluorite | Level 6

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

Giuliano
Fluorite | Level 6

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.

ballardw
Super User

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.

Giuliano
Fluorite | Level 6

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.

Reeza
Super User

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?

Giuliano
Fluorite | Level 6

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.

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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