how to add a row

[PGStats]

@hashman, Wow, that's a very nice demonstration, and some very instructive explanations. Please continue to impress us with hash based solutions. Speaking for myself, this is one area where I don't feel so confident. Thank you.

[hashman]

@PGStats, You're most welcome. I have no choice but to continue as a penance for the audacity of triggering this SAS hash thingy in the first place almost exactly 20 years ago ;).

Best

Paul D.  

[ChrisNZ]

@PGStats Thanks. I never used cross join before. One more in the toolbox... 🙂

[PGStats]

If you don't mind reordering the y's :

data eq;
input ID y x ;
cards;
1 1 27 
1 0 . 
1 . 30 
1 2 38 
2 . 23 
2 0 32  
2 2 . 
3 0 33 
3 1 21 
3 2 13 
4 1 56 
4 0 67 
;


data want;
array v{0:3} _temporary_;

do i = lbound(v) to hbound(v); v{i} = 0; end;

do until(last.id);
    set eq; by id;
    v{coalesce(y, 3)} = x;
    end;

do y = ., 0, 1, 2;
    x = v{coalesce(y, 3)};
    output;
    end;

drop i;
run;

proc print data=want noobs; run;

[hashman]

@PGStats,

Extremely ingenious. Kudos! And I'm sure you realize that it's constrained by the assumptions that:

(a) EQ has no records with Y not in (. 0 1 2)   

(b) EQ has no variables but ID, X, and Y.   

But within the limits of the sample data as presented, I can't think of anything more clever or concise.

Best

Paul D.

[hashman]

@xiangpang, 

There're many ways to do what you want. I'll show primarily how a hash table can be used to keep track of what you have and what - not. 

First, if your data are actually sorted by ID (as in your sample):

data eq ;                          
  input ID y x ;                   
  cards ;                          
1 1 27                             
1 0  .                             
1 . 30                             
1 2 38                             
2 . 23                             
2 0 32                             
2 2  .                             
3 0 33                             
3 1 21                             
3 2 13                             
4 1 56                             
4 0 67                             
;                                  
run ;                              
                                   
data want ;                        
  if _n_ = 1 then do ;             
    dcl hash h (multidata:"y") ;   
    h.definekey ("y") ;            
    h.definedone () ;              
  end ;                            
  set eq ;                         
  by ID ;                          
  output ;                         
  h.add() ;                        
  if last.ID ;                     
  x = 0 ;                          
  do y = . , 0 to 2 ;              
    if h.check() ne 0 then output ;
  end ;                            
  h.clear() ;                      
run ;                              

If EQ is not initially ordered:

data _null_ ; 
 dcl hash h (multidata:"y", ordered:"a") ;
 h.definekey ("id", "y") ; 
 h.definedata ("id", "y", "x") ; 
 h.definedone () ; 
 dcl hash a () ; 
 a.definekey ("id") ; 
 a.definedone () ; 
 dcl hiter i ("a") ; 
 do until (z) ; 
   set eq end = z ; 
   h.add() ; 
   a.ref() ; 
 end ; 
 x = 0 ; 
 do while (i.next() = 0) ; 
   do y = . , 0 to 2 ; 
     h.ref() ; 
   end ; 
 end ; 
 h.output (dataset:"want") ; 
run ; 
                 

A nice extra of this step is that your data will come out sorted by both ID and Y.

If you're averse to using the SAS hash object, the same as in step  #! can be done using olde goode arrays (minisclule since you only have 4 values to track). The following again assumes that EQ is sorted by ID:

data want (keep = ID x y) ;          
  array _f [-1:2] ;                  
  array _v [-1:2] (. 0 1 2) ;        
  do until (last.id) ;               
    set eq ;                         
    by ID ;                          
    output ;                         
    if nmiss (y)       then _f[-1] = 1 ;   
    else if y in (0:2) then _f[ y] = 1 ;   
  end ;                              
  x = 0 ;                            
  do j = lbound (_f) to hbound (_f) ;
    if _f[j] then continue ;         
    y = _v[j] ;                      
    output ;                         
  end ;                              
run ;                                

HTH

Pail D.