topic Re: Lag in SAS Programming

Lag

SASPhile — Thu, 01 Aug 2019 20:36:02 GMT

There are about 100o records with below data structure

ID Stat value
11 n 20
11 mean 12
11 median 12.5
12 n 0
12 mean 0
12 median 0

if the stat n =0 mean and median should be set to blanks as below:

ID Stat value
11 n 20
11 mean 12
11 median 12.5
12 n 0
12 mean
12 median

Re: Lag

ballardw — Thu, 01 Aug 2019 22:45:31 GMT

Perhaps:

data want;
   set have;
   by id;
   retain nflag;
   if first.id then nflag=0;
   if stat='n' and value=0 then nflag=1;
   if nflag and stat in ('mean','median') then call missing (value);
   drop nflag;
end;

Assumes the data is sorted by ID and that the N statistic always comes before the mean and median.

Re: Lag

hashman — Thu, 01 Aug 2019 22:47:52 GMT

@SASPhile :

It can done only if VALUE is a character variable. Is it? If not, it can be set a a missing value, not a blank. But you can set the system option missing="" and then see numeric missing values as a blank through whatever UI you're using. The code below should work regardless of the data type of VALUE.

First, if stat="n" always on the first record in each ID group, then:

data have ;                                                               
  input ID Stat $ value ;                                                 
  cards ;                                                                 
11   n           20                                                       
11   mean        12                                                       
11   median    12.5                                                       
12   n            0                                                       
12   mean         0                                                       
12   median       0                                                       
;                                                                         
run ; 
                                                                   
option missing = "" ;                                                                          

data want (drop = _:) ;                                                   
  do until (last.id) ;                                                    
    set have ;                                                            
    by id ;                                                               
    if first.id and put (cats (value), $1.) = "0" then _setnull = 1 ;     
    if _setnull and stat in ("mean", "median") then call missing (value) ;
    output ;                                                              
  end ;                                                                   
run ;

If stat="n" (and also "mean" or "median") can be anywhere within an ID group, then, more generally:

data want (drop = _:) ;                                                   
  do _n_ = 1 by 1 until (last.id) ;                                       
    set have ;                                                            
    by id ;                                                               
    if stat = "n" and put (cats (value), $1.) = "0" then _setnull = 1 ;   
  end ;                                                                   
  do _n_ = 1 to _n_ ;                                                     
    set have ;                                                            
    if _setnull and stat in ("mean", "median") then call missing (value) ;
    output ;                                                              
  end ;                                                                   
run ;

Either way, CATS and CALL MISSING are used to make the code independent of the data type of VALUE.

Kind regards

Paul D.