Desktop productivity for business analysts and programmers

Month-on-Month Change

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Month-on-Month Change

Hi,

 

I have a table which is a list of transactions, with customer numbers and products.

 

I want to group transactions by customer and month and find the months where volume changes >10% vs the last month they transacted (which may be several months before).

 

Ideally the output would show Customer, Month, Volume, Change vs Last Month Traded.

 

I am very new with SAS and have no idea how to do this, any help would be great.

 

Thanks

 

Jamie


Accepted Solutions
Solution
‎01-18-2018 08:54 AM
Super User
Posts: 10,530

Re: Month-on-Month Change

proc sort data=summed;
by customer_num year month;
run;

data want;
set summed;
by customer_num;
change_percent = auto_percent - lag(auto_percent);
if first.customer_num then change_percent = .;
run;

You probably won't need the sort, depending on how you created the sum.

Note that I don't use non-standard variable names like 'AUTO%'n. Special characters have their place in variable labels.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Super User
Posts: 9,799

Re: Month-on-Month Change

Hi,

 

I would suggest if you are new to SAS then start with the training material:

http://video.sas.com/#category/videos/how-to-tutorials

 

You can follow this up by looking through the forums here, there is a lot of information.

 

After that if you have further questions, then post a question here, supply test data in the form of a datastep (as we cannot see your data or structure) using the code window (which is the {i} above post area) and following this post if need:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Further, post example required output so we can inputs/outputs.

 

At a guess from what you say you can do it in a number of ways, first group the data using proc means/summary, or with proc sql.  Then a datastep with a diff() and calculation.  E.g.

proc sql;
  create table SUMMED as
  select  CUSTOMER,
             MONTH,
             sum(VOLUME) as VOLUME
  from   HAVE
  group by CUSTOMER,
                 MONTH;
quit;

data want;
  set summed;
  if lag(customer)=customer then change=diff(volume);
run;
Occasional Contributor
Posts: 18

Re: Month-on-Month Change

[ Edited ]

Thanks for your help, although I am still unsure how to proceed.

 

Here is the code I have from Query Builder, I need to add another column which is Auto% Change - this needs to be a measure of the change against the previous month for the customer, so should be blank/null for the first month for each customer.

 

I am not sure how to use the DATA STEP.

 

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_ALL_VOL_0000 AS 
   SELECT t1.CUSTOMER_NUM, 
          t1.YEAR, 
          t1.MONTH, 
          t1.'AUTO%'n
      FROM WORK.QUERY_FOR_ALL_VOL t1;
QUIT;

Thanks

Super User
Super User
Posts: 9,799

Re: Month-on-Month Change

You can put code in code blocks, I believe that there is an option to do these code blocks in EG.  Not used Query builder before, I prefer writing the code myself.  You can see from that generated code you are missing a sum() and a group by, how you get those in using QB I don't know, its far simpler to write the code into a code block following what I provided.

Solution
‎01-18-2018 08:54 AM
Super User
Posts: 10,530

Re: Month-on-Month Change

proc sort data=summed;
by customer_num year month;
run;

data want;
set summed;
by customer_num;
change_percent = auto_percent - lag(auto_percent);
if first.customer_num then change_percent = .;
run;

You probably won't need the sort, depending on how you created the sum.

Note that I don't use non-standard variable names like 'AUTO%'n. Special characters have their place in variable labels.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 18

Re: Month-on-Month Change

Posted in reply to KurtBremser

Thanks, this has worked great. 

 

As a further step I would like to add another column in showing LAG2 (another step back). How can I change the 'if first' part to account for this - it would be 'if second' that I need but I don't think this is a function.

 

Thanks again

Super User
Posts: 10,530

Re: Month-on-Month Change


jagnew wrote:

Thanks, this has worked great. 

 

As a further step I would like to add another column in showing LAG2 (another step back). How can I change the 'if first' part to account for this - it would be 'if second' that I need but I don't think this is a function.

 

Thanks again


Add a counter variable:

data want;
set summed;
by customer_num;
change_percent = auto_percent - lag(auto_percent);
change_percent_2 = auto_percent - lag2(auto_percent);
if first.customer_num
then do;
  counter = 1;
  change_percent = .;
  change_percent_2 = .;
end;
else do;
  counter + 1;
  if counter = 2 then change_percent_2 = .;
end;
drop counter;
run;

The use of the increment syntax

counter + 1;

instead of the normal addition syntax

counter = counter + 1;

makes SAS automatically retain the variable, otherwise a retain statement would be needed.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 18

Re: Month-on-Month Change

Posted in reply to KurtBremser

Thanks Kurt, much appreciated

Super User
Posts: 23,954

Re: Month-on-Month Change

[ Edited ]

If you're going to use the GUI tools in EG you need to break things down into small steps. 

 

1. Summarize the data to have to get the totals by customer/month -> Summarize Task or Query Builder

2. Sort to ensure order is correct -> Sort Task or in Step 1 of Query Builder

3. Calculate difference between last month -> data step is the only way I know, use either DIF or LAG function to obtain previous value

5. Filter to include only records of interest -> not actually sure what you want here yet, but WHERE clause

 

This also depends on how your data is stored. 

 

If you're learning to code this is much simpler, something along the lines of:

 

proc means data=have noprint nway;
class customer month;
var <your var>;
output out=summary_table1 sum(<your var>) = total_sales;
run;

proc sort data=summary_table1;
by customer month;
run;

data prev_value;
set summary_table1;
by customer;

prev_value = lag(total_sales);
if first.customer then prev_value = .;
 if prev_value ne . then want = total_sales/ prev_value -1;
run;

data want;
set prev_value;
where want > <threshold>;
run;

 

 


jagnew wrote:

Hi,

 

I have a table which is a list of transactions, with customer numbers and products.

 

I want to group transactions by customer and month and find the months where volume changes >10% vs the last month they transacted (which may be several months before).

 

Ideally the output would show Customer, Month, Volume, Change vs Last Month Traded.

 

I am very new with SAS and have no idea how to do this, any help would be great.

 

Thanks

 

Jamie


 

Occasional Contributor
Posts: 18

Re: Month-on-Month Change

Thanks Reeza, I think I'll come back to this method when I'm a bit more capable

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 239 views
  • 3 likes
  • 4 in conversation