我有以下数据:
wei 01feb2018 car
wei 02feb2018 car
wei 02mar2019 bike
carlin 01feb2018 car
carlin 05feb2018 bike
carlin 07mar2018 bike
carlin 01mar2019 car
我想识别新客户和持续客户,如果客户在过去 12 个月内没有购买过商品,那么它将成为新客户。所需的输出就像
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 都为新客户并继续。
我正在尝试但不明白逻辑 -
proc sql;
select a.*,(select count(name) from t where intnx("month",-12,a.date) >= 356)
as tot
from t a;
Quit;
您似乎需要两个不同的“状态”变量,一个用于连续性over前一年和连续性的一年within month.
在 SQL 中,存在自反相关子查询结果可以是满足以下条件的行的案例测试over and within标准。日期算术用于计算相隔天数和INTCK
用于计算间隔月份:
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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)