Identify record with negative value in a specific variable

[Spawn77]

Hello everyone, 

Here is an example for which I have some difficulties:

data test;

length VAR1 $ 1; input VAR1 $ VAR2 VAR3;

datalines;

A 1 157

A 2 130

A 3 67

A 4 -54

A 5 -89

A 6 -101

B 1 500

B 2 456

B 3 234

B 4 -10

B 5 -303

B 6 -409

C 1 239

C 2 210

C 3 150

C 4 -2

C 5 -30

C 6 -44

The number of records for A, B, C etc is fixed and equal to 6.

VAR3 is sorted from high to low and contains negative values.

The steps to perform are the following:

1) For each VAR1 (A, B, C etc) identify VAR2 and VAR3 for which VAR3 is negative for the first time

2) Identify VAR2 and VAR3 of the record just above

3) Make an operation with values obtained from 1) and 2). See example below:

Example: for VAR1 = A

1) VAR2 = 4 VAR3=-54

2) VAR2 = 3 VAR3=67

3) Make an operation with 1) and 2):      4+ (4-3)*[67/(67-(-54))]

Thank you!

[PeterClemmensen · Accepted solution]

Welcome to the SAS Communities 🙂 If your data represents your actual problem, then do something like this

data test;
length VAR1 $ 1; input VAR1 $ VAR2 VAR3;
datalines;
A 1 157
A 2 130
A 3 67
A 4 -54
A 5 -89
A 6 -101
B 1 500
B 2 456
B 3 234
B 4 -10
B 5 -303
B 6 -409
C 1 239
C 2 210
C 3 150
C 4 -2
C 5 -30
C 6 -44
;

data temp;
   merge test test(firstobs=2 rename=(VAR2=_VAR2 VAR3=_VAR3));
   newvar=_VAR2+(_VAR2-VAR2)*(VAR3 / (VAR3-_VAR3));
   if VAR3 gt 0 & _VAR3 lt 0;
   format newvar 8.2;
run;

[hashman]

I like the terse 2-pass solution by @PeterClemmensen . However, it can also be done in a single pass, while avoiding unnecessary computations: 

data have ;                                                       
  input var1: $1. var2 var3 ;                                     
  cards ;                                                         
A 1  157                                                          
A 2  130                                                          
A 3   67                                                          
A 4  -54                                                          
A 5  -89                                                          
A 6 -101                                                          
B 1  500                                                          
B 2  456                                                          
B 3  234                                                          
B 4  -10                                                          
B 5 -303                                                          
B 6 -409                                                          
C 1  239                                                          
C 2  210                                                          
C 3  150                                                          
C 4   -2                                                          
C 5  -30                                                          
C 6  -44                                                          
run ;                                                             
                                                                  
data need ;                                                       
  do until (last.var1) ;                                          
    set have ;                                                    
    by var1 ;                                                     
    if N (var) then continue ;                                    
    if var3 < 0 then do ;                                         
      var = var2 + (var2 - _var2) * divide (_var3, _var3 - var3) ;
      output ;                                                    
    end ;                                                         
    _var2 = var2 ;                                                
    _var3 = var3 ;                                                
  end ;                                                           
run ;                                                             

Kind regards

Paul D.