找到数据集中最大的连续的日期-sas实现
目录
亲测有效
sas实现
/*目标:找到数据集中最大的连续的日期?*/
/*制作只有日期的单列数据集*/
data a;
do i="01Jan2010"d to "20Jan2010"d by 1;
if i^="02Jan2010"d and i^="04Jan2010"d and i^="10Jan2010"d and i^="17Jan2010"d then output;
end;
format i yymmdd10.;
run;
proc sort data=a; by i;run;
/*查找过程*/
data b;
retain j grp cnt;
set a;
by i;
if _N_=1 then do;
j=i;
grp=0;
cnt=1;
end;
else do;
if i-j=1 then do;
cnt+1;
end;
else do;
grp+1;
cnt=1;
end;
j=i;
end;
/*drop j;*/
format j yymmdd10.;
run;
/*展示*/
proc sql;
create table c as
select grp,max(cnt) as max ,min(i) format yymmdd10. as start_date,max(i) format yymmdd10. as end_date
from b group by grp
;quit;
/*proc sql;*/
/*create table c as*/
/*select*/
/*a.i*/
/*from b a*/
/*inner join (select grp,max(cnt) as max from b group by grp having max>=4) b*/
/*on a.grp=b.grp*/
/*;*/
/*quit;*/
/*如果是两个变量求*/
/*制作数据集*/
data a;
retain id date fg;
do date="01Jan2010"d to "20Jan2010"d by 1;
fg=0;
if date^="02Jan2010"d and date^="04Jan2010"d and date^="10Jan2010"d and date^="17Jan2010"d
then do
fg=1 ;
end;
if date<="05Jan2010"d then id=1;
else id=2;
output;
end;
format date yymmdd10.;
run;
/*处理数据集*/
data a;
set a;
where fg=1;
run;
proc sort data=a;
by id date;run;
quit;
data b;
retain id_new date_new grp cnt;
set a;
by id date;
if _N_=1
then do;
id_new=id;
date_new=date;
grp=0;
cnt=1;
end;
else do;
if date-date_new=1 and id=id_new
then do;
cnt+1;
end;
else if (id=id_new) and date-date_new>1 then do;
grp+1;
cnt=1;
end;
else do;
grp=0;
cnt=1;
end;
date_new=date;
id_new=id;
end;
/*drop date_new id_new;*/
keep id date fg grp cnt;
run;
data b;
retain id date fg grp cnt;;
set b;
run;
/*制作数据集测试*/
data a;
do i=1 to 5 by 1;
output;
end;
run;
mysql实现
SELECT period_state, MIN(date) as start_date, MAX(date) as end_date
FROM (
SELECT
success_date AS date,
"succeeded" AS period_state,
IF(DATEDIFF(@pre_date, @pre_date := success_date) = -1, @id, @id := @id+1) AS id
FROM Succeeded, (SELECT @id := 0, @pre_date := NULL) AS temp
UNION
SELECT
fail_date AS date,
"failed" AS period_state,
IF(DATEDIFF(@pre_date, @pre_date := fail_date) = -1, @id, @id := @id+1) AS id
FROM Failed, (SELECT @id := 0, @pre_date := NULL) AS temp
) T WHERE date BETWEEN "2019-01-01" AND "2019-12-31"
GROUP BY T.id
ORDER BY start_date ASC
SELECT
success_date AS date,
"succeeded" AS period_state,
IF(DATEDIFF(@pre_date, success_date) = -1, @id, @id := @id+1) AS id ,
IF(DATEDIFF(@pre_date, success_date) = -1, @red := @red+1,@red:=1 ) AS red,
@pre_date := success_date
FROM riqi, (SELECT @id := 0, @pre_date := NULL ,@red := 1) AS temp