SAS程序员测试（二）

shiyiming

data tem;
retain ord 0;
set tem;
x=lag(vol);
if vol=x then ord=ord;else ord=ord+1;
run;

proc sort;by id ord date;run;

data first last;
set tem;
by id ord;
if first.ord then output first;
if last.ord then output last;
run;

data final;
merge first (rename=(date=date1))
      last (rename=(date=date2));
by id ord;
run;
proc print;
var id date1 date2 vol;
run;

shiyiming

I think this is the simplest solution:

data res(drop=date);
  set tem;
  by id vol notsorted;
  retain bdate edate;
  format bdate yymmdd10. edate yymmdd10.;
  if first.vol then bdate=date;
  if last.vol then do;
    edate=date;
    output;
  end;
run;

shiyiming

data tem;
input id:$1. date:yymmdd10. vol;
format date yymmdd10.;
cards;
0 20020101 0
1 20020101 10
1 20020102 10
1 20020103 10
1 20020108 9
1 20020109 9
1 20020110 9
2 20020101 9
2 20020102 8
2 20020103 14
2 20020104 8
2 20020108 11
2 20020109 11
2 20020110 12
;run;
data tem_1;
retain s 0;
set tem;
if _n_>1 & (id ^=lag(id) | vol ^=lag(vol)) then s+1;
run;
proc sort data=tem_1;
by s;
run;
data tem_2;
set tem_1;
by s;
if first.s  then output;
if last.s then output;
run;
proc sort data=tem_2;
by s;
run;
data result;
format sdate edate yymmdd10.;
set tem_2;
by s;
sdate=lag(date);
edate=date;
if last.s=1;
run;

shiyiming

proc sort data=tem;
by id vol date;
run;
data rst(drop=date);
set tem;
by id vol;
format sdate yymmdd10.;
format edate yymmdd10.;
if first.vol then sdate=date;
if last.vol then do;
  edate=date;
  output;
end;
retain;
run;

shiyiming

data tem;
input id:$1. date:yymmdd10. vol;
format date yymmdd10.;
cards;
0 20020101 0
1 20020101 10
1 20020102 10
1 20020103 10
1 20020108 9
1 20020109 9
1 20020110 9
2 20020101 9
2 20020102 8
2 20020103 14
2 20020104 8
2 20020108 11
2 20020109 11
2 20020110 12
;run;

proc sql;
select distinct id, min(date) as SDate format=yymmdd10., max(date) as EDate format=yymmdd10., Vol as New_Vol from tem group by id, vol;
quit;

shiyiming

data Sasuser.test;
input id:$1. date:yymmdd10. vol;
format date yymmdd10.;
cards;
0 20020101 0
1 20020101 10
1 20020102 10
1 20020103 10
1 20020108 9
1 20020109 9
1 20020110 9
2 20020101 9
2 20020102 8
2 20020103 14
2 20020104 8
2 20020108 11
2 20020109 11
2 20020110 12
;run;
proc sql;
create table sasuser.result as
select id,min(date) as starttime,max(date) as endtime,vol from Sasuser.test group by id,vol;
run;
proc print data=sasuser.result;
format starttime YYMMDD10.;
format endtime YYMMDD10.;
run;

shiyiming

基本同意topgun_li的作法，但从题目字面看，还是应排序

proc sort data=tem;
  by id vol;
run;

data result;
  set tem;
  by id vol;
  if first.vol then sdate=date;
  if last.vol then do;
    edate=date;
    new_vol=vol;
    output;
  end;
  keep id sdate edate new_vol;
  format sdate yymmdd10. edate yymmdd10.;
  retain sdate;
run;

shiyiming

nice guy！
cool!

shiyiming

如果原数据中的日期可以重复, 也就是说同一天有2个VOL值(也许不可能发生,但允许数据录入错误), 很多程序就可能有问题! 我试改了一个数, 测试一下看看! 欢迎讨论!!

input id:$1. date:yymmdd10. vol;
format date yymmdd10.;
cards;
0 20020101 0
1 20020101 10
1 20020102 10
1 20020103 10
1 20020108 9
1 20020109 9
1 20020110 9
2 20020101 9
2 20020102 8
2 20020108 14    /* 将原来的 20020103 改成 20020108 */
2 20020104 8
2 20020108 11
2 20020109 11
2 20020110 12
;
run;

shiyiming

好久没有看这个帖子了。

谢谢xsmile！，其实这个问题用SQL就可以了，没有必要用Data步。

这个模拟数据是一个优化库存数据存储的模板，好比如仓库存储中productId,date,hold_volume，因为可能多种产品好多日的存量一直数量不变，
所以改用startDate，endDate的存储方式更加简捷。

不可能出现某日，某个productId的重复数据，否则搞不清楚该日该产品的存量到底是多少？