topic Re: How to repeat last row of an observation in SAS Programming

How to repeat last row of an observation

sas_student1 — Fri, 27 Sep 2019 21:20:40 GMT

Hello,

Have a dataset of clients with admission (admit), discharge(discharge) and in between (inbtwn) years.

What I would like to do is to have an additional row at the end of each clients group of observation that would have the final discharge year in the inbtwn column.

This is what I have:

Client	Admit	Dsch	Inbtwn
1	2014	2019	2015
1	2014	2019	2016
1	2014	2019	2017
1	2014	2019	2018
2	2013	2015	2014
3	2015	2018	2016
3	2015	2018	2017

This is what I want:

Client	Admit	Dsch	Inbtwn
1	2014	2019	2015
1	2014	2019	2016
1	2014	2019	2017
1	2014	2019	2018
1	2014	2019	2019
2	2013	2015	2014
2	2013	2015	2015
3	2015	2018	2016
3	2015	2018	2017
3	2015	2018	2018

Notice the last rows for each client in bold, under the "inbtwn" column we have the year 2019 for client 1, 2015 for client 2 and 2018 for client 3 these were not in the original database.

Im sure I need to use a retain and last. but cant figure out how.

Below is the SAS code that makes the table.

data have;
informat client 1. admit dsch inbtwn;
input client admit dsch inbtwn;
datalines;
1	2014 2019 2015
1	2014 2019 2016
1	2014 2019 2017
1	2014 2019 2018
2	2013 2015 2014
3	2015 2018 2016
3	2015 2018 2017
;
run;

Any help would be appreciated!

Thank you

Re: How to repeat last row of an observation

novinosrin — Fri, 27 Sep 2019 21:30:46 GMT


data have;
informat client 1. admit dsch inbtwn;
input client admit dsch inbtwn;
datalines;
1	2014 2019 2015
1	2014 2019 2016
1	2014 2019 2017
1	2014 2019 2018
2	2013 2015 2014
3	2015 2018 2016
3	2015 2018 2017
;
run;

data want;
 do until(last.client);
 set have;
 by client;
 output;
 end;
 inbtwn=inbtwn+1;
 output;
run;

Re: How to repeat last row of an observation

PGStats — Fri, 27 Sep 2019 22:02:05 GMT

@novinosrin, I agree that this is the cleanest way to do this, but I think that given OP's request,

inbtwn=inbtwn+1;

should be

inbtwn=dsch;

Re: How to repeat last row of an observation

novinosrin — Fri, 27 Sep 2019 22:07:00 GMT

Sir @PGStats You read my mind. Well, you always do and have. I was just trying to think slightly intuitively and amuse myself. 🙂 Indeed, your point is actually absolute and valid.

Re: How to repeat last row of an observation

hashman — Sat, 28 Sep 2019 03:10:58 GMT

@sas_student1:

I like the DoW-loop scheme proposed by @novinosrin (and corrected by @PGStats) and the DoW-loop in general (as anyone familiar with my SAS-related writings can tell). However, methinks in this case it's a bit of an overkill since you can simply do:

data have ;                       
  input client admit dsch inbtwn ;
  cards ;                         
1 2014 2019 2015                  
1 2014 2019 2016                  
1 2014 2019 2017                  
1 2014 2019 2018                  
2 2013 2015 2014                  
3 2015 2018 2016                  
3 2015 2018 2017                  
;                                 
run ;                             
                                  
data want ;                       
  set have ;                      
  by client ;                     
  output ;                        
  if last.client ;                
  inbtwn = dsch ;                 
  output ;                        
run ;

Kind regards

Paul D.

Re: How to repeat last row of an observation

PGStats — Sat, 28 Sep 2019 04:04:03 GMT

Well, you know the story. When you have a hammer in your hand, everything sort of looks like a nail. Thanks for the simplification @hashman .

Re: How to repeat last row of an observation

hashman — Sat, 28 Sep 2019 04:34:55 GMT

@PGStats:

True! In this vein, folks who know their DoW-loop tend to think in terms of its logic (me included). But sometimes you have to step back and ask yourself if this is what the task calls for. I've caught myself in this loop (no pun intended) more than once.

Some 17 years ago I wrote in "The Magnificent DO", arguing for using an explicit DO loop instead of the implied loop when the former fit the logic of the task better: "Such a rigid approach is practically tantamount to forcing a program into the fixed cage of an existing programming construct. However, programming is hardly meant to be this way. Does it not make more sense to choose the tool best befitting the task, rather than to tweak the task in order to fit the tool?" Well, this paradigm goes both ways, doesn't it? ;).

Kind regards

Paul D.

Re: How to repeat last row of an observation

Tom — Sat, 28 Sep 2019 04:43:30 GMT

The subject line is a little garbled. And observation is a row.

In general to conditionally add an observation you need to have an OUTPUT statement, but once you have any OUTPUT statement in the data step then SAS does not add the automatic OUTPUT at the end of the step. So you will need two OUTPUT statements.

output;
if last.id then do;
  inbtwn=dsch;
  output;
end;

Why do you need this? Are there other variables on the dataset? If not it looks like you can generate the observations you want from the FIRST observation.

data want;
  set have ;
  by id admit;
  if first.admit then do ibtwn=admit+1 to dsch;
     output;
  end;
run;

Also what do the records look like when ADMIT=DSCH? What value would IBTWN have in that case? ADMIT+1 would be larger than DSCH.

Re: How to repeat last row of an observation

hashman — Sat, 28 Sep 2019 19:10:31 GMT

@Tom:

"In general to conditionally add an observation you need to have an OUTPUT statement, but once you have any OUTPUT statement in the data step then SAS does not add the automatic OUTPUT at the end of the step. So you will need two OUTPUT statements."

True. However, as a matter of programming deviancy, in this case we can get away with the single OUTPUT via the subterfuge of:

data have ;                                     
  input client admit dsch inbtwn ;              
  cards ;                                       
1 2014 2019 2015                                
1 2014 2019 2016                                
1 2014 2019 2017                                
1 2014 2019 2018                                
2 2013 2015 2014                                
3 2015 2018 2016                                
3 2015 2018 2017                                
;                                               
run ;                                           
                                                
data want ;                                     
  set have ;                                    
  by client ;                                   
  do _n_ = 0 to last.client ;                   
    inbtwn = ifn (_n_, dsch, inbtwn) ;          
  * if _n_ then inbtwn = dsch ; * same result ; 
    output ;                                    
  end ;                                         
run ;

Of course, this program is "better" only in the sense of amusing a curious SAS programmer with "What's this code is doing?" kind of puzzle ;).

Kind regards

Paul D.