Hello. I am working with customer data that looks like this:
Order_id | Customer_Id | order_date | Product_id | Quantity |
1 | c1 | 1/23/2014 | P1 | 10 |
2 | c1 | 1/23/2016 | P1 | 4 |
3 | c2 | 2/5/2015 | P2 | 6 |
4 | c2 | 6/6/2015 | P2 | 2 |
I would like to find customers whose quantity is decreasing over time based on product_ID and Customer_ID. My final table would like this:
Customer | Product_ID | N order | N-1 Order | Difference |
C1 | P1 | 10 | 4 | 6 |
C2 | P2 | 6 | 2 | 4 |
My original idea is to use Proc SQL but I am having trouble figuring out how to the combine the rows based on customer and procuct id. Can anyone provide some help?
A data step solution could be
data have;
input Order_id$ Customer_Id$ order_date:mmddyy10. Product_id$ Quantity;
format order_date mmddyy10.;
datalines;
1 c1 1/23/2014 P1 10
2 c1 1/23/2016 P1 4
3 c2 2/5/2015 P2 6
4 c2 6/6/2015 P2 2
;
proc sort data = have;
by Customer_Id Product_id;
run;
data want;
format Customer_Id Product_id lag_N_Order N_Order Difference;
set have;
by Customer_Id Product_id;
lag_N_Order = lag1(Quantity);
N_Order = Quantity;
Difference = lag_N_Order - N_Order;
keep Customer_Id Product_id N_Order lag_N_Order Difference;
if last.Customer_Id & last.Product_id & Difference>0;
run;
First, sort by customer, product_id and order_date.
Then, in a data step, use
data want;
set have;
by customer product_id;
n_order = quantity;
n_order_prev = lag(quantity);
difference = n_order_prev - n_order;
if not first.product_id then output;
drop quantity;
run;
The lag() function retrieves the previous value of a certain variable, it works as a FIFO chain.
Same approach (i.e., data step), but I don't think you need to create extra variables:
data decreases; set have; by customer_id Product_id; if quantity lt lag(quantity) and first.Product_id eq 0 then output; run;
Art, CEO, AnalystFinder.com
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.