topic Re: compare values in different rows in different variables. in SAS Procedures

compare values in different rows in different variables.

deleted_user — Mon, 28 Dec 2009 16:25:46 GMT

Hi, I am trying to compare values in different observations in
different variables.
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
Price C.
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.

EX:
if price A ^="." then do;
If price A (obs4) >= Price C(obs3) then Cnt = "+";
If price A (obs4)<= Price B (obs3) then Cnt = "-";
end;

DataSet
obs Price A Price B Price C
1 . 1115.75 1116
2 . 1115.75 1116
3 . 1115.75 1116
4 1115 . .
5 . 1115.75 1116
6 1116 . .
7 . 1115.75 1116
8 . 1115.75 1116
9 . 1115.75 1116
10 1116.5 . .
11 1116 . .
12 . 1115.75 1116
13 . 1115.75 1116
14 . 1115.75 1116
15 1116 . .
16 . 1115.75 1116
17 . 1115.75 1116
18 . 1115.75 1116
19 . 1115.75 1116
20 1116 . .
21 . 1115.75 1116
22 . 1115.75 1116
23 . 1115.75 1116
24 . 1115.75 1116
25 1116 . .
26 1116.25 . .
27 1116.5 . .
28 . 1115.75 1116.5
29 1116.5 . .

Re: compare values in different rows in different variables.

sbb — Mon, 28 Dec 2009 17:24:00 GMT

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.

Scott Barry
SBBWorks, Inc.

Re: compare values in different rows in different variables.

Patrick — Tue, 29 Dec 2009 11:11:59 GMT

"...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:

data demo;
set have;
retain Br Cr;
if _n_=1 then
do;
Br=b; Cr=c;
end;
else
do;

if a ne '.' then
do;
...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.
Br=b; Cr=c;
end;

end;
run;

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?

HTH
Patrick