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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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.

View solution in original post

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User
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
Obsidian | Level 7

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
Super User

@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
Obsidian | Level 7

Thanks Kurt, much appreciated

Reeza
Super User

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
Obsidian | Level 7

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 3820 views
  • 3 likes
  • 4 in conversation