Month-on-Month Change

[jagnew]

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

[RW9]

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;

[jagnew]

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

[RW9]

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.

[Kurt_Bremser · Accepted solution]

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.

[jagnew]

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

[Kurt_Bremser]

---

@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.

[jagnew]

Thanks Kurt, much appreciated

[Reeza]

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

---

[jagnew]

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