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;
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;
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
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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.