topic Re: Delete last record conditionally in SAS Programming

Delete last record conditionally

Q1983 — Fri, 20 Sep 2019 17:43:00 GMT

data have;

format mydate date9.;

input ln_no $ code $ mydate date9.;

datalines;

1123 C 1jun2018

1123 C 11jun2018

1123 A 15jun2018

1123 A 12jun2018

1123 R 9jun2018

1123 R 3jun2018

1123 A 3May2018

1123 C 12jun2017

;

run;

proc sort data= have out= have2; by ln_no descending mydate;run;

How can I delete the last record if the code is anything except an A. So in this case I want to eliminate the 1123 C 12jun2017 because the code does not equal A

Re: Delete last record conditionally

hashman — Fri, 20 Sep 2019 17:51:15 GMT

@Q1983:

data have ;                                                                                                                             
  format mydate date9.;                                                                                                                 
  input ln_no $ code $ mydate date9. ;                                                                                                  
  cards ;                                                                                                                               
1123 C 1jun2018                                                                                                                         
1123 C 11jun2018                                                                                                                        
1123 A 15jun2018                                                                                                                        
1123 A 12jun2018                                                                                                                        
1123 R 9jun2018                                                                                                                         
1123 R 3jun2018                                                                                                                         
1123 A 3May2018                                                                                                                         
1123 C 12jun2017                                                                                                                        
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data want ;                                                                                                                             
  set have end = z ;                                                                                                                    
  if z and code ne "A" then delete ;                                                                                                    
run ;

Kind regards

Paul D.

Re: Delete last record conditionally

Jagadishkatam — Fri, 20 Sep 2019 17:53:50 GMT

Please try

data want;
set have2 end=eof;
if eof and code ne 'A' then delete;
run;

Re: Delete last record conditionally

novinosrin — Fri, 20 Sep 2019 17:56:55 GMT

Hi @Jagadishkatam looks like the same answer has been provided by Guru PD 🙂

Re: Delete last record conditionally

Jagadishkatam — Fri, 20 Sep 2019 17:59:56 GMT

Alternatively with nobs option

data want;
set have2 nobs=nobs;
lastobs=nobs;
if _n_=lastobs and code ne 'A' then delete;
run;

Re: Delete last record conditionally

Jagadishkatam — Fri, 20 Sep 2019 18:01:12 GMT

Yes I did not check that answer and when i posted it was same, now i changed the answer with nobs option.

Re: Delete last record conditionally

Q1983 — Fri, 20 Sep 2019 18:04:07 GMT

Lets say I have multiple entries by number like this
data have ;
format mydate date9.;
input ln_no $ code $ mydate date9. ;
cards ;
1123 C 1jun2018
1123 C 11jun2018
1123 A 15jun2018
1123 A 12jun2018
1123 R 9jun2018
1123 R 3jun2018
1123 A 3May2018
1123 C 12jun2017
1126 R 9jun2018
1126 R 3jun2018
1127 A 3May2018
1127 C 12jun2017
;
run ;

data want ;
set have end = z ;
if z and code ne "A" then delete ;
run ;
Can the code be modified to accomplish the same delete of the last record based on number if it is not an A

Re: Delete last record conditionally

Q1983 — Fri, 20 Sep 2019 18:05:57 GMT

I should have used multiple numbers in my example. Can I accomplish the same thing if I have multiple ln_no
data have ;
format mydate date9.;
input ln_no $ code $ mydate date9. ;
cards ;
1123 C 1jun2018
1123 C 11jun2018
1123 A 15jun2018
1123 A 12jun2018
1123 R 9jun2018
1123 R 3jun2018
1123 A 3May2018
1123 C 12jun2017
1126 R 9jun2018
1126 R 3jun2018
1127 A 3May2018
1127 C 12jun2017
;
run ;

data want ;
set have end = z ;
if z and code ne "A" then delete ;
run ;

Re: Delete last record conditionally

Jagadishkatam — Fri, 20 Sep 2019 18:05:58 GMT

Yes just replace the code ne "A" with the numeric variable and its value

Re: Delete last record conditionally

Q1983 — Fri, 20 Sep 2019 18:10:30 GMT

Did not understand your answer. In this case I want to eliminate the last entry which works in your code you sent however I want to also eliminate 1126 R 9jun2018
1126 R 3jun2018 because netiher has a code of A. Also eliminate 1123 C 12jun2017 because it was not preceeded by a code of A, FInally I want to eliminate 1123 C 1jun2018 because it was not preceeded by a code of A

Re: Delete last record conditionally

hashman — Fri, 20 Sep 2019 18:25:37 GMT

@Q1983;

Methinks you've misstated the problem, hence the confusion. As I understand now, what you meant is "Delete the last record in each BY group if its code is not A". If so:

data have ;                                  
  format mydate date9.;                      
  input ln_no $ code $ mydate date9. ;       
  cards ;                                    
1123 C 1jun2018                              
1123 C 11jun2018                             
1123 A 15jun2018                             
1123 A 12jun2018                             
1123 R 9jun2018                              
1123 R 3jun2018                              
1123 A 3May2018                              
1123 C 12jun2017                             
1126 R 9jun2018                              
1126 R 3jun2018                              
1127 A 3May2018                              
1127 C 12jun2017                             
;                                            
run ;                                        
                                             
data want ;                                  
  set have ;                                 
  by ln_no ;                                 
  if last.ln_no and code ne "A" then delete ;
run ;

See if that serves you right.

Kind regards

Paul D.

Re: Delete last record conditionally

Reeza — Fri, 20 Sep 2019 18:26:28 GMT

use first/last.

data want;
set have;
by ln_no;

if last.ln_no and code ='C' then delete;
run;

Re: Delete last record conditionally

ballardw — Fri, 20 Sep 2019 22:55:55 GMT

@Q1983 wrote:

data have;

format mydate date9.;

input ln_no $ code $ mydate date9.;

datalines;

1123 C 1jun2018

1123 C 11jun2018

1123 A 15jun2018

1123 A 12jun2018

1123 R 9jun2018

1123 R 3jun2018

1123 A 3May2018

1123 C 12jun2017

;

run;

proc sort data= have out= have2; by ln_no descending mydate;run;

How can I delete the last record if the code is anything except an A. So in this case I want to eliminate the 1123 C 12jun2017 because the code does not equal A

Notice there is no example of the desired/ expected output. So we have to guess what you actually mean and with out changes in the first variable it can't be determined what the actual role of that variable would be in the solution.