Hi, I am trying to compare values in different observations in
Whenever there is value on Price A,I would like to compare the value
on Price A to the last values that shows before "." on Price B and
For example, compare obs 4 on Price A to obs3 Price B and obs3 Price C.
compare obs10 value on Price A to obs9 Price B and obs9 Price C
compare obs11 value on Price A to obs9 Price B and obs9 Price C
Since they are not in same line, I am having hard time comparing them. I
used lag function to line up on those values in different variables
but it doesn't work in obs 10 and obs 11 or obs 25,26,27 case.
if price A ^="." then do;
If price A (obs4) >= Price C(obs3) then Cnt = "+";
If price A (obs4)<= Price B (obs3) then Cnt = "-";
Explore using the LAG function and/or capture the prior obs value (using RETAIN to a new-named variable). Also, you will likely need to explore using BY GROUP PROCESSING (hint: a search argument for you to use against SAS.COM for doc references), and IF FIRST. and/or IF LAST. in order to keep track of "related" variable values as you process your SAS files.
"...I would like to compare the value on Price A to the last values that shows before "." on Price B and Price C...."
Is that now the value of B and C the last time A was not missing - or is this the last time B and C was not missing? Not clear to me - and however I interprete it the case "...compare obs11 value on Price A to obs9 Price B and obs9 Price C..." doesn't make sense to me. Should obs11 be compared to B and C from obs10?
Whatsoever: I believe you will need to use 'retain'. Something like this:
retain Br Cr;
if _n_=1 then
if a ne '.' then
...compare a with b and c...
...assign the vars b and c to the retained ones as this iteration will be the last time 'a' was not missing during the next iteration when a is not missing.
You still will have to think what to do the first time 'a' is not missing. To what values of b and c do you want to compare?