用SAS编个小程序，看看是谁经常光顾这家小店

我有这样一个顾客名单，它有谁哪天光顾了这家小店。请大侠帮我写段macro程序，按照姓名和电话号码，生成在X天的区间内逛店次数是Y的顾客名单，比如；

在2014/01/01-2014/01/05这个5天的区间内逛店次数是2的顾客名单，以及每个人的各次订单价值；
在2014/01/02-2014/01/06这个5天的区间内逛店次数是2的顾客名单，以及每个人的各次订单价值；
等等。

数据举例如下：


DATA visit_history;
   INPUT  name $ 1-4 phone @11 visit_date date11. @22 order ;
CARDS;
John 1234 12 Jan 2014 120
John 1234 15 Jan 2014 100
John 5000 12 Nov 2014 200
Mark 7896 18 Jun 2014 500
;
RUN;
PROC PRINT DATA=visit_history;
format visit_date  date11.;
RUN;

变量说明：

name姓名；
phone 电话号码
visit_date逛店日期
order订单价值

注意，数据中的第2个人和第3个人的名字一样但是电话不同，虽然同名，但这是两个人而非同一个人。

如果以上描述不清楚的话，请尽可按照你的理解来写程序，对于任何具有参考价值的程序，我都答谢。

%macro test(num=, inds=, outds=, date_start=, date_end=);
proc sql noprint;
        create table &outds(drop=visitnum namephone) as
        select count(namephone) as visitnum, namephone, name, phone, visit_date, order
        from (select catx(" ",name,phone) as namephone, name, phone, visit_date, order from &inds
                having ("&date_start"d <= visit_date <= "&date_end"d))
        group by  namephone
        having  visitnum=&num
        order by  namephone;       
quit;

%if not &sqlrc and &sqlobs=0 %then
        %put NO NAME SATIFYS CONDITION: &num visits between &date_start and &date_end;

%mend test;

%test(num=1, inds=visit_history, outds=want,  date_start=12JAN2014, date_end=15JAN2014)

proc sort data=visit_history;
by name;
run;
data visitor;
set visit_history;
count+1;
by name;
if first.name then count=1;
if last.name then output;
run;

dddddddddddd

%macro test(num=, inds=, outds=, date_start=, date_end=);
proc sql noprint;
        create table &outds(drop=visitnum) as
        select count(name) as visitnum, name, phone, visit_date, order
        from (select name, phone, visit_date, order from &inds
                having ("&date_start"d <= visit_date <= "&date_end"d))
        group by name
        having calculated visitnum=&num and visit_date
        order by name;       
quit;

%if not &sqlrc and &sqlobs=0 %then
        %put NO NAME SATIFYS CONDITION: &num visits between &date_start and &date_end;

%mend test;

%test(num=3, inds=visit_history, outds=want,  date_start=12JAN2014, date_end=15JAN2014)

jl60156 发表于 2014-12-15 08:17
%macro test(num=, inds=, outds=, date_start=, date_end=);
proc sql noprint;
        create table &outds( ...

多谢回复，很详细的一段代码。注意，数据中的第2个人和第3个人的名字一样但是电话不同，虽然同名，但这是两个人而非同一个人。 请问能再修改一下你的这段程序吗？

现在我能想到的是，我可先根据名字和电话创建一个新的名字变量然后应用你的代码。不知还有其他解决方法没有。

jl60156 发表于 2014-12-15 04:53
%macro test(num=, inds=, outds=, date_start=, date_end=);
proc sql noprint;
        create table &outds( ...

在这个subquery中是不是应该用where而不是having呢？（见下）

having ("&date_start"d <= visit_date <= "&date_end"d))

能不能给科普一下，having和where的用法选择呢？是不是说，只要能用where的地方就能用having，但是如有summary function，例如count，就只能用having？

多谢科普！

Differences between the HAVING  Clause and WHERE Clause
HAVING clause attributes	WHERE clause attributes
is typically used to specify conditions for including  or excluding groups of rows from a table.	is used to specify conditions for including or  excluding individual rows from a table.
must follow the GROUP BY clause in a query, if used  with a GROUP BY clause.	must precede the GROUP BY clause in a query, if used  with a GROUP BY clause.
is affected by a GROUP BY clause; when there is no  GROUP BY clause, the HAVING clause is treated like a WHERE clause.	is not affected by a GROUP BY clause.
is processed after the GROUP BY clause and any  aggregate functions.	is processed before a GROUP BY clause, if there is one,  and before any aggregate functions.

Taken from SAS user guide

jl60156 发表于 2014-12-16 10:47
Taken from SAS user guide

遇到大侠了啊！失敬失敬