Proc Sql Question on Rolling Dates with Conditions

[Ksharp]

Hi NiuNiu

Yes.

I admit that Cartesian Product of sql is a little faster than data step.

But data step 's Cartesian Product is more powerful, which can do more than sql.

Especially for some complex situation, I prefer to use data step.

SQL is designed based on Cartesian Product, so maybe it is optimized by some skill.

And data step need to open dataset each time of iteration, so it is maybe the cause to waste more time than sql.

So for some simple problem, I recommend to use sql, But for some complicated problem which sql can not accomplish. The only choice is data step

Thanks!

Ksharp

[Ksharp]

Hi. I recode it by using proc sql.

But not know how fast will it be .

data temp;
input account $ date : mmddyy10. test id;
format date mmddyy10.;
cards;
384000 6/30/2010 1 13.15
384000 6/15/2010 1 13.14     
384000 6/10/2010 1 13.14     
384000 6/1/2010  1 13.13
386000 5/30/2010 1 12.12
386000 5/15/2010 1 12.12
386000 4/25/2010 1 12.13
;
run;
proc sql;
 create table op as
  select a.*,b.date as _date ,b.id as _id ,case
                                              when a.id=b.id then 1
                                              when b.id is missing then 1
                                               else 0
                                            end as count
   from temp as a left join temp as b
    on a.account=b.account and b.date between a.date-30 and a.date-7;

 create table flag as
  select account,date,not(sum(count)) as flag
   from op
    group by account,date;


 create table want as
  select a.*,flag
   from temp as a left join flag as b
    on a.account=b.account and a.date=b.date
    ;
quit;

Ksharp