问题描述
我有以下数据:
wei 01feb2018 car
wei 02feb2018 car
wei 02mar2019 bike
carlin 01feb2018 car
carlin 05feb2018 bike
carlin 07mar2018 bike
carlin 01mar2019 car
我想识别新客户和持续客户,如果客户在过去 12 个月内没有购买过商品,那么它将成为新客户.所需的输出就像
I want to identify new and continue customers, if a customer had no purchase in last 12 months then it will become a new customer. Required output be like
wei 01feb2018 car new
wei 02feb2018 car cont.
wei 02mar2019 bike new
carlin 01feb2018 car new
carlin 05feb2018 bike cont.
carlin 07mar2018 bike cont.
carlin 01mar2019 car new
现在,如果客户在同一个月为前客户购买了任何物品,1 月 1 日购买了汽车,1 月 15 日购买了自行车,那么我希望两个将客户 a 分类为 1 月的新客户,在另一个报告中我希望客户 a作为新的和继续.
Now if a customer has purchased any item in the same month for ex -customer a purchased car on 01jan and bike on 15jan then I want two classify customer a as new for Jan for one report and in another report I want customer a as both new and continue.
我正在尝试但没有得到逻辑 -
I'm trying but not getting the logic -
proc sql;
select a.*,(select count(name) from t where intnx("month",-12,a.date) >= 356)
as tot
from t a;
Quit;
推荐答案
您似乎需要两个不同的状态"变量,一个用于连续性超过上一年,一个用于连续性在 月.
You appear to want two different 'status' variables, one for continuity over prior year and one for continuity within month.
在 SQL 中,存在自反相关子查询结果可以是满足 over 和 within 标准的行的案例测试.日期算法用于计算天数,INTCK
用于计算月数:
In SQL an existential reflexive correlated sub-query result can be a case test for rows meeting the over and within criteria. Date arithmetic is used to compute days apart and INTCK
is used to compute months apart:
data have; input
customer $ date& date9. item& $; format date date9.; datalines;
wei 01feb2018 car
wei 02feb2018 car
wei 02mar2019 bike
carlin 01feb2018 car
carlin 05feb2018 bike
carlin 07mar2018 bike
carlin 01mar2019 car
run;
proc sql;
create table want as
select *,
case
when exists
(
select * from have as inner
where inner.customer=outer.customer
and (outer.date - inner.date) between 1 and 365
)
then 'cont.'
else 'new'
end as status_year,
case
when exists
(
select * from have as inner
where inner.customer=outer.customer
and outer.date > inner.date
and intck ('month', outer.date, inner.date) = 0
)
then 'cont.'
else 'new'
end as status_month
from have as outer
;
quit;
这篇关于Proc sql:基于回溯期的新客户和持续客户的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!