Help with Do Loop to replicate Lag operation

[hhchenfx]

Hi Everyone,

I have the below data. I want to find record that:

Price>Level and Price[i+1]>Level[i+1] and Price[i+2]>Level[i+2] and and Price[i+3] LESS THAN Level[i+3].

Record 2 meet that condition and I want to create new variable say good=1.

I can do it easily with lag since Lag allow me to put record in the order.

Now I want to do it with Do Loop in the but the code seem to be too long since I need to go step by step.

Is there any better way to handle the Do loop like counting the time condition met combine with incorporating the order so that Do loop can be done in a better manner?

----------added----------
I finally find a way to incorporate the order into a Sum condition measure ( the last code section).

This case, anytime t=3, it is the record I need.

Even though I did it, I do not truly understand the algorithm behind it. Is there any general method so that in the future, if I have to check 4 record, I can easily accomplish it?

Thank you for your help.

HHC

data have;
  input time price level;
  datalines;
1     5     9
2     6     3
3     8     5
4     10    5
5     2    6
6 5 100
7 5 9
;run;

*LAG method;
proc sort; by decending time;run;
data have; set have;
drop lp1-lp3 ll1-ll3;
lp1=lag1(price);
lp2=lag2(price);
lp3=lag3(price);

ll1=lag1(level);
ll2=lag2(level);
ll3=lag3(level);

if price>level and lp1>ll1 and lp2>ll2 and lp3<ll3 then Good_lag=1;run;

*DO LOOP method;

proc sort; by time;run;

data want;

drop i;
set have nobs=totalobs;
i+1;

if price > level and i le totalobs-2 then do j=i+1 to i+1;
set have (keep = price level rename=(price=prc level=lv)) point=j;
if prc>lv then do k=j+1 to j+1;
set have (keep = price level rename=(price=prc level=lv)) point=k;
  if prc>lv then do l=k+1 to k+1;
  set have (keep = price level rename=(price=prc level=lv)) point=l;
  if prc<lv then GOOD=1;
  end;
end;
end;

run;

*final method;

data want2;

drop i;

set want nobs=totalobs;

i+1;

t=0;

if price>level and i le totalobs-3 then do j=i+1 to i+3;

set want (keep = price level rename=(price=prc1 level=lv1)) point=j;

if prc1>lv1 then t=t+(j-i+1)*3;

else if prc1<=lv1 then t=t-(j-i+1)*3;

end;

else t=.;

run;

data want2; set want2;

if t=3 then Good_luck=1;

run;

[PGStats]

FIRSTOBS dataset option might be a better option , unless you want to expand the list of lags:

data have;

  input time price level;

  datalines;

1     5     9

2     6     3

3     8     5

4    10     5

5     2     6

6     5   100

7     5     9

;

data want;

if not end1 then set have(firstobs=2 rename=(price=pr1 level=le1)) end=end1;

if not end2 then set have(firstobs=3 rename=(price=pr2 level=le2)) end=end2;

if not end3 then set have(firstobs=4 rename=(price=pr3 level=le3)) end=end3;

set have;

good_lag = price>level and pr1>le1 and pr2>le2 and pr3<le3;

drop pr1-pr3 le1-le3;

run;

proc print data=want noobs; run;

PG

[KachiM]

SAS Data Step is very powerful but many times it may not be obvious

as in your case. If you translate your objective differently you

would have hit upon my idea.

[1] You count the number of times the condition:

        price > level

is met.

[2] When the condtion fails and if the count is 3 or more then you

have succeeded.

If you apply this idea, Data Step is so simple requiring mere 6

statements. No need for lag-functions or other SET with POINTER=

options.

You have not specified on how to deal with when price = level.

If you want this condition, you simply make the condtion as:

        price >= level

To have more data to check my program, I have replicated your

data once again as in:

data have;

input time price level;

datalines;

1   5   9

2   6   3

3   8   5

4  10   5

5   2   6

6   5 100

7   5   9

1   5   9

2   6   3

3   8   5

4  10   5

5   2   6

6   5 100

7   5   9

;

run;

I have not dropped the count from the output data set so that

you can see how the count is used to terminate the sequence. You

may drop it, if needed.

data want;

   set have end = eof;

   indicator = 'NOGOOD';

   if price <  level then do; if count >= 3 then indicator = 'GOOD'; count = 0; end;

   else if (price > level) then count + 1;

run;

proc print data = want;

run;

Good Luck.

Muthia Kachirayan

[gergely_batho · Accepted solution]

Hi HHC,

I basically only reorganized the code. The original code is more compact, I find no problem with that.

I am using _N_ instead of i. I do not use t, instead I have a leave statement, when a condition is not met. No need for the additional data step, I am settin Good_luck=1 in the same data step.

If you need to look ahead more, just increase the numbers in bold.

Is it a gaming industry example?

data want2;

set want nobs=totalobs;

if price>level and _N_ <= totalobs-3 then do j=_N_+1 to _N_+3;

     set want (keep = price level rename=(price=prc1 level=lv1)) point=j;

     if j < _N_+3 then do;/*lag(-1)...lag(-2)*/

          if prc1>lv1 then do;/*at lag(-1), lag(-2) we check >*/

               /*do nothing! It is like incrementing t in your original code: t=t+1; */

          end;

          else do;

               leave; /*break out from the loop, Good_luck=.*/

          end;

     end;

     else do;/*lag(-3)*/

          if prc1<=lv1 then do;/*at lag(-3) we check <=*/

               Good_luck=1;

          end;

     end;

end;/*end of loop and if*/

run;

[hhchenfx]

Thank you for your help.

I like the method of breaking j into

if j < _N_+3 then do for lag12

and else for lag3

That's how the order can be controlled.

HHC