topic Re: averaging two of three values in SAS Programming

averaging two of three values

paolamejia — Fri, 03 Aug 2018 02:12:32 GMT

Hi,

I'm new to SAS and this is my first post.

I have a small dataset with body measurements. I have three different measurements for the same variable, and I want to average only two of these values, the closest ones. How do I do this?

My data looks something like this:

obs weight1 weight2 weight3

1 54 . 54.7 53

2 48 . 47.5 . .

3 67.4 . 68 . 69

4 . 48 . 48 .

I'm using version 9.4

I thought I could write a code to compare the sums of weight 1+2, 2+3 and 1+3, and average the smallest quantity, but I don't know how to ask for an absolute value. Thanks!

Re: averaging two of three values

novinosrin — Fri, 03 Aug 2018 02:18:25 GMT

Not able to understand your desired output for the sample. Can you post an output sample for the input sample explaining your requirement plz

Re: averaging two of three values

paolamejia — Fri, 03 Aug 2018 04:03:34 GMT

Thanks for replying!

I need to create a new variable that is the average of two of the values, the two closest ones. So where there are only two measurements, the new variable is the average of those two. But if there are three measurements (the third is taken when the discrepancy between the first two is larger than one unit), I want the average to be of only two values, the two closest values.

Re: averaging two of three values

paolamejia — Fri, 03 Aug 2018 04:32:14 GMT

Trying to clarify my question:

I have these data

obs weight1 weight2 weight3

1 53 54.7 53

2 48 47.5

3 67.4 68.5 69

4 48 48 .

The output I want is:

obs weight1 weight2 weight3 finalweight

1 53 54.7 53 53

2 48 47.5 47.8

3 67.4 68.5 69 68.8

4 48 48 48

Where weight3 is a missing value the new variable is the mean of weight1 and weight2. But when there are three different weights, I only want to use two of them to calculate the mean. Maybe calculating the absolute value of every possible sum of pairs, and then asking SAS to compute the mean of the closest values?

Re: averaging two of three values

novinosrin — Fri, 03 Aug 2018 04:41:10 GMT

data have;
input obs weight1 weight2 weight3 ;
cards;
1     54          54.7        53
2      48          47.5       .  
3     67.4        68          69
4    48          48           .
;

data w;
set have;
n=_n_;
array  t(*) weight:;
k=n(of t(*));
if k=3 then 
do i=1 to 2;
do j=i+1 to 3;
k1=t(i);
k2=t(j);
dif=abs(t(j)-t(i));
output;
end;
end;
else do;avg=mean(weight1,weight2,weight3); output; end;
run;

proc sort data=w out=_w ;
by n  dif;
run;

data want;
set _w;
by n;
if first.n;
if first.n and not last.n then 
avg=mean(k1,k2);
keep n weight: avg;
run;

Re: averaging two of three values

ChrisNZ — Fri, 03 Aug 2018 05:14:30 GMT

Like this?

data WANT; 
  set HAVE;
  DIFF12=abs(WEIGHT1-WEIGHT2);
  DIFF13=abs(WEIGHT1-WEIGHT3);
  DIFF23=abs(WEIGHT2-WEIGHT3);
  if DIFF12=min(of DIFF:) then AVG=mean(WEIGHT1,WEIGHT2);
  if DIFF13=min(of DIFF:) then AVG=mean(WEIGHT1,WEIGHT3);
  if DIFF23=min(of DIFF:) then AVG=mean(WEIGHT2,WEIGHT3);
run;

Re: averaging two of three values

paolamejia — Fri, 03 Aug 2018 05:17:44 GMT

Wow, thanks so much! It seems to work great for the observations that have three weight measurements, but what happens with the observations that only have two measurements? They don't seem to come out in the final output, data want, which has only two observations.

I'm going to try it with my own small dataset

Re: averaging two of three values

hashman — Fri, 03 Aug 2018 05:19:18 GMT

@paolamejia:

1. How are you going to handle the missing values in defining the "closest"? I assume you're ignoring them.

2. How are you going to handle the ties between two or more pairs of values that are equally close? I assume that you pick the first ones from left to right.

Your sample data aren't rich enough to test an algorithmic concept. So, try the following input first:

data have ; 
  call streaminit (7) ; 
  array w weight1-weight5 ; 
  do _n_ = 1 to 5 ; 
    do over w ; 
      w = rand ("integer", 23) ; 
      if rand ("uniform") < .3 then w = . ;
    end ; 
    output ; 
  end ; 
run ;

Now, the program:

data want (keep = w: mean closest:) ;
  set have ;                         
  array w [*] w: ;                   
  diff = constant ("big") ;          
  do i = 1 to dim (w) - 1 ;          
    if nmiss (w[i]) then continue ;  
    do j = i + 1 to dim (w) ;        
      if nmiss (w[j]) then continue ;
      _diff = abs (w[i] - w[j]) ;    
      if _diff > diff then continue ;
      diff = _diff ;                 
      _i = i ;                       
      _j = j ;                       
    end ;                            
  end ;                              
  closest1 = w[_i] ;                 
  closest2 = w[_j] ;                 
  mean = mean (of closest:) ;        
run ;

Note that it's not much different in principle from what @novinosrin has offered, save for a couple of generalizations. It generates the following output:

weight1 weight2 weight3 weight4 weight5 closest1 closest2 mean

19	.	8	22	4	19	22	20.5
4	.	19	21	7	19	21	20.0
10	9	15	23	11	10	11	10.5
3	.	.	.	17	3	17	10.0
7	.	10	.	2	7	10	8.5

Now that we can see that the concept works, we can move on to your sample data set:

data have ;              
  input weight1-weight3 ;
  obs = _n_ ;            
  cards ;                
54    54.7  53           
48    47.5   .           
67.4  68    69           
  .   48    48           
;                        
run ;

Running the program above against it, we get:

weight1 weight2 weight3 closest1 closest2 mean

54.0	54.7	53	54.0	54.7	54.35
48.0	47.5	.	48.0	47.5	47.75
67.4	68.0	69	67.4	68.0	67.70
.	48.0	48	48.0	48.0	48.00

If you can't discern the idea from the code, it compares each weight to each other pairwise. If the distance between them is shorter than the previously found distance (initially set to an impossibly big number), then the respective array indices are stored, and the process is repeated until the pairing is exhausted. If instead of the very first indices with the same closest distance you want the very last ones, change the inequality sign from > to =>.

HTH

Paul D.

Re: averaging two of three values

paolamejia — Fri, 03 Aug 2018 05:32:39 GMT

Yes! Works like a charm. Thank you so much!

Re: averaging two of three values

paolamejia — Fri, 03 Aug 2018 05:43:14 GMT

Thank you so much for looking at this. You are right, I'm ignoring the missing values.

What you and @novinosrin are proposing is more complex than what I just saw from @ChrisNZ, but the latter seems to work just fine. I don't know what it does when there are ties, my database is really small (153 observations) and I'm only comparing three variables.

I can't discern the idea from the code (yet) but I'll certainly look at those alternatives to get more familiar with SAS. Thanks again!

Re: averaging two of three values

hashman — Fri, 03 Aug 2018 06:08:52 GMT

@ChrisNZ:

Tis' nice, especially for handling the ignoring of the nulls.

And terse ... for the number of weights N=3, which certainly fits the scope of the OP's question.

Now, how would you apply/extend this code to N=10? N=100?

Best

Paul D.

Re: averaging two of three values

hashman — Fri, 03 Aug 2018 06:47:57 GMT

If you really want to learn SAS, you have to look beyond the data you have at hand and ask yourself how your code will work and whether it will work when the data change - because it WILL change. Coding for three variables like 12, 13, 23 means putting data (in this case, how many variables you have and what their combinations are for the task at hand) in your program. A properly designed program is only a set of instructions to process data; it shouldn't contain any data itself. That means, in particular:

- No IF-THEN-ELSE "walpaper" code. The hard coded values in the would-be wallpaper should be stored in a table outside the program, and the program should look them up.

- No hard coded constants. These, too, belong to a control file outside the program. The program should read the control file and get the values from it.

- No hard coding relying on a specific dimension, such as the number of variables. The latter, again, belongs to a control file or, much less preferably, macro variables or macro parameters.

Any aspiring SAS programmer should dutifully absorb Ian Whitlock's paper "Code or Data?":

http://www2.sas.com/proceedings/sugi24/Advtutor/p40-24.pdf

to understand these issues and also understand that a poor program design may serve one's immediate purpose but bite one in the derriere when it's least expected. I'm eternally grateful to Ian for his savage bluntness on SAS-L of yore when it came to this (and other) kind of matters. There's a saying that a good physicist sees analogies but a great one seen analogies between analogies. With SAS programming, it's no different.

Paul D.

Re: averaging two of three values

ChrisNZ — Fri, 03 Aug 2018 06:49:22 GMT

Haha! Golf challenge?

Can't test atm, but something along these lines I suppose (pseudo code made by modifying your code):

data WANT (keep = W: MEAN ) ;
  set HAVE;                         
  array W[*] W: ; 
  call symputx('____dim',dim(W));
  array Z[&____dim.] _temporary_;
  do I = 1 to &____dim.  ;   
    Z[I]=W[I]
  end;
  call sortn(of Z[*]);                  
  do I = 1 to &____dim. - 1 ;          
    DIFF = abs (Z[I] - Z[I+1]) ;    
    if . < DIFF < MINDIFF then do ;
      MINDIFF = DIFF ;                 
      MEAN=mean(Z[I], Z[I+1]) ;                       
    end;                            
  end;                              
run ;

Would this work?

Re: averaging two of three values

hashman — Fri, 03 Aug 2018 07:14:58 GMT

Ha-ha! Why not. I like your idea, and it should work if further developed. SYMPUTX won't do what you want because it's a run-time instruction, while arrays are sized at compile time, so the value produced by SYMPUTX cannot size an array in the same DATA step. But methinks it will sure work in the context of the following if you adjust the code somewhat to handle the generation of missing values on the line DIFF=.

data have ;                                
  call streaminit (7) ;                    
  array w weight1-weight5 ;                
  do _n_ = 1 to 5 ;                        
    do over w ;                            
      w = rand ("integer", 23) ;           
      if rand ("uniform") < .3 then w = . ;
    end ;                                  
    output ;                               
  end ;                                    
run ;      
                                
%let dim = 5 ;                  
data WANT (keep = W: MEAN ) ;      
  set HAVE;                        
  array W[*] W: ;                  
  array Z[&dim] _temporary_;       
  do I = 1 to &dim  ;              
    Z[I]=W[I] ;                    
  end;                             
  call sortn(of Z[*]);             
  do I = 1 to &dim - 1 ;           
    DIFF = abs (Z[I] - Z[I+1]) ;   
    if . < DIFF < MINDIFF then do ;
      MINDIFF = DIFF ;             
      MEAN=mean(Z[I], Z[I+1]) ;    
    end;                           
  end;                             
run ;

Have fun!

Best

Paul D.

Re: averaging two of three values

ChrisNZ — Fri, 03 Aug 2018 10:50:36 GMT

Yes, I thought about using symput being a silly idea right after I typed it.

This is a bit better than my previous version (still untested) if one wants to only keep a single step.

Why adjust the code somewhat to handle the generation of missing values on the line DIFF=. ?

data WANT (keep = W: MEAN ) ;
  set HAVE;                         
  array W[*] W: ; 
  array Z[9999] _temporary_;
  do I = 1 to dim(W) ;   
    Z[I]=W[I]
  end;
  call sortn(of Z[*]);                  
  do I = 9999 to 1 by -1 while (^missing(Z[I-1])) ;          
    DIFF = abs (Z[I] - Z[I-1]) ;    
    if DIFF < MINDIFF then do;
      MINDIFF = DIFF ;                 
      MEAN=mean(Z[I], Z[I-1]) ;                       
    end;                            
  end;                              
run ;

Re: averaging two of three values

novinosrin — Fri, 03 Aug 2018 14:08:48 GMT

"I'm eternally grateful to Ian for his savage bluntness on SAS-L of yore when it came to this (and other) kind of matters"

Wow a great person's gesture owards a perhaps "equal" which is so amazing, humble, eloquent,modest and respectful. I don't know Ian Whitlock, nor i have SAS L account but have heard a lot about him through Art Tabachneck, so guessing.

That's note a lot of people like me and others should take seriously in all walks of life. That speaks a lot from heart and soul. Thank you!

The day If and when I become PD. Oh well taking your blessing if even beyond PD, I will echo a similar note to somebody else.

Re: averaging two of three values

hashman — Fri, 03 Aug 2018 20:31:45 GMT

@ChrisNZ:

As I've said, I like your idea - because sorting turns a O(n**2) problem (pairwise comparison) into O(n*log(n)) problem (heap sort behind the scenes). Also, the ordering makes it unnecessary to call the ABS function since you always subtract something smaller or equal. Note, however, that you're not scaling the code you first posted but present an altogether different approach instead. Your new untested code won't work just yet ; but it will with a couple of adjustments:

1. Since it is a minimum that is being calculated, the initial value should be set to something bigger than any possible MINDIFF. In your code, it is auto-set to missing, which is less than anything but _. and which is why MEAN comes out missing.

2. Since temp array elements are retained, it should be cleaned up for each new record, otherwise the leftovers from the previous records mess up the values relied upon during the calculation.

Thus, working (and tested) code:

data WANT (keep = W: MEAN ) ;                           
  set HAVE ;                                            
  array W [*] W: ;                                      
  array Z [9999] _temporary_;                           
  do I = 1 to dim(W) ;                                  
    Z[I] = W[I] ;                                       
  end;                                                  
  call sortn (of Z[*]) ;                                
  MINDIFF = Z [dim(Z)] ;                                
  do I = dim (Z) to 1 by -1 while (^ missing (Z[I-1])) ;
    DIFF = Z[I] - Z[I-1] ; * Note: no ABS ;                              
    if DIFF < MINDIFF then do ;                          
      MINDIFF = DIFF ;                                  
      MEAN = mean (Z[I], Z[I-1]) ;                      
    end ;                                               
  end ;                                                 
  call missing (of Z{*]) ;                              
run ;

Of course, performance-wise, sizing the temp array as "big enough" has its impact on sorting. That can be alleviated by using an ordered hash table instead of the temp array, as the former grows and shrinks dynamically. If I find time, I'll post this kind of variant on the theme.

Best

Paul D.

p.s. I said "adjust the code somewhat to handle the generation of missing values" because in my testing, this line generated missing values. Above, it no longer does.

Re: averaging two of three values

hashman — Fri, 03 Aug 2018 22:12:59 GMT

@ChrisNZ:

As tentatively promised in the earlier reply, here's your sorted list approach encapsulated using a hash table. Note that _N_ below is used merely as a container for W-values in the table.

data want (keep = w: mean ) ;                
  if _n_ = 1 then do ;                       
    dcl hash h (multidata:"Y", ordered:"A") ;
    h.definekey ("_n_") ;                    
    h.definedone () ;                        
    dcl hiter i ("h") ;                      
  end ;                                      
  set have ;                                 
  array w w: ;                               
  do over w ;                                
    if N (w) then h.add(key:w, data:w) ;     
  end ;                                      
  _dm = max (of w:) ;                        
  do _q = 1 by 1 while (i.next() = 0) ;      
    if _q > 1 then do ;                      
      _d = _n_ - _pn ;                       
      if _d < _dm then do ;                  
        _dm = _d ;                           
        mean = mean (_n_, _pn) ;             
      end ;                                  
    end ;                                    
    _pn = _n_ ;                              
  end ;                                      
  h.clear() ;                                
run ;

I wish, though, that in addition to the hash object we had a simpler linear dynamic structure (sort of like a list in Python). Of course, the hash object can play the role, but a list would provide for simpler and terser coding and less overhead in situations like the case at hand.

Best

Paul D.

Re: averaging two of three values

ChrisNZ — Sat, 04 Aug 2018 06:45:53 GMT

@hashman Good points.

1. In order to get a MEAN even if there is a single value we need to alter the loop slightly so it iterates at least once.

Taking care of this can also take care of the missing initial value for MINDIFF.

2. I thought I didn't need to reset to zero as I refill the array with every observation. But of course and all values go to the end when I sort it, so this was wrong. We just need to fill the back of the array to avoid that then.

So something this may work better (untested):

data WANT (keep = W: MEAN ) ;                           
  set HAVE ;                                            
  array W [*] W: ;                                      
  array Z [9999] _temporary_;                           
  do I = 1 to dim(W) ;                                  
    Z[10000-I] = W[I] ;                                       
  end;                                                  
  call sortn (of Z[*]) ;                                
  do I = 9999 to 2 by -1 until (missing (Z[I-1])) ;
    DIFF = Z[I] - Z[I-1] ;                              
    if . < DIFF < MINDIFF or MEAN = . then do ;                          
      MINDIFF = DIFF ;                                  
      MEAN = mean (Z[I], Z[I-1]) ;                      
    end ;                                               
  end ;                                
run ;

Yes hash tables require a lot more code, Furthermore:

3. You needn't have multidata in the hash table.

4. I wouldn't initialise

_dm = max (of w:) ;

because the logic then fails if the values are negative.

Re: averaging two of three values

hashman — Sat, 04 Aug 2018 08:22:21 GMT

@ChrisNZ:

1. Yes.

2. Takes care of business. Missing values will still be generated at the DIFF= line, but it's just a matter of log notes. Plus, WHILE (^) instead of UNTIL () can fix that.

3. Yes, I do - if we want the same assumptions. To wit, your code treats ties as "the closest" and computes the mean accordingly. Mine, too, except that I'm getting the lowest ties (since I ascend) and you - the highest (since you descend). This can be aligned on my side if I use ORDERED:"D" (but not on yours, as SORTN always sorts ascending and you run descending for obvious reasons). If I omit MULTIDATA:"Y", there will be no ties; and the result will change accordingly.

4. Agreed; a good catch. Better to set it to constant("big"), as I did initially, to cover all bases.

Best

Paul D.