BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MB_Analyst
Obsidian | Level 7

I have data:

 

 

 

data have;
	input id type $ date $;
datalines;
1	c 2019-08-01
1	a 2019-08-04
1 	b 2019-08-05
1 	c 2019-08-11
2	c 2019-10-13
2	c 2019-10-14
2	a 2019-10-20
2       b 2019-10-25
;

 

 

I want to remove observations that occur before type a, if they are not type a. Therefore, each ID should have their first observation being equal to type a. 

 

Desired output:

 

 

data want;
	input id type $ date $;
datalines;
1	a 2019-08-04
1 	b 2019-08-05
1 	c 2019-08-11
2	a 2019-10-20
2      b 2019-10-25
;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
hashman
Ammonite | Level 13

@MB_Analyst:

Try this:

data have ;                                                                                                                                                                                                                                                     
  input id type $ date :$10. ;                                                                                                                                                                                                                                  
datalines;                                                                                                                                                                                                                                                      
1  c  2019-08-01                                                                                                                                                                                                                                                
1  a  2019-08-04                                                                                                                                                                                                                                                
1  b  2019-08-05                                                                                                                                                                                                                                                
1  c  2019-08-11                                                                                                                                                                                                                                                
2  c  2019-10-13                                                                                                                                                                                                                                                
2  c  2019-10-14                                                                                                                                                                                                                                                
2  a  2019-10-20                                                                                                                                                                                                                                                
2  b  2019-10-25                                                                                                                                                                                                                                                
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
data want ;                                                                                                                                                                                                                                                     
  do _n_ = 0 by 0 until (last.id) ;                                                                                                                                                                                                                             
    set have ;                                                                                                                                                                                                                                                  
    by id ;                                                                                                                                                                                                                                                     
    if type = "a" then _n_ = 1 ;                                                                                                                                                                                                                                
    if _n_ then output ;                                                                                                                                                                                                                                        
  end ;                                                                                                                                                                                                                                                         
run ;                     

Kind regards

Paul D. 

View solution in original post

2 REPLIES 2
Astounding
PROC Star

Assuming your data set is already sorted by ID:

 

data want;
   del_flag = 'N';
   do until (last.id);
      set have;
      by ID;
      if type='a' then del_flag = 'Y';
   end;
   do until (last.id);
      set have;
      by ID;
      if type='a' then del_flag = 'N';
      if del_flag = 'N' then output;
   end;
   drop del_flag;
run;

This assumes that if an ID has no type "a" observations, you want to keep all its observations.  It's actually simpler if you want to delete them all instead:

 

data want;
   del_flag = 'N';
   do until (last.id);
      set have;
      by ID;
      if type='a' then del_flag = 'N';
      if del_flag = 'N' then output;
   end;
   drop del_flag;
run;
hashman
Ammonite | Level 13

@MB_Analyst:

Try this:

data have ;                                                                                                                                                                                                                                                     
  input id type $ date :$10. ;                                                                                                                                                                                                                                  
datalines;                                                                                                                                                                                                                                                      
1  c  2019-08-01                                                                                                                                                                                                                                                
1  a  2019-08-04                                                                                                                                                                                                                                                
1  b  2019-08-05                                                                                                                                                                                                                                                
1  c  2019-08-11                                                                                                                                                                                                                                                
2  c  2019-10-13                                                                                                                                                                                                                                                
2  c  2019-10-14                                                                                                                                                                                                                                                
2  a  2019-10-20                                                                                                                                                                                                                                                
2  b  2019-10-25                                                                                                                                                                                                                                                
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
data want ;                                                                                                                                                                                                                                                     
  do _n_ = 0 by 0 until (last.id) ;                                                                                                                                                                                                                             
    set have ;                                                                                                                                                                                                                                                  
    by id ;                                                                                                                                                                                                                                                     
    if type = "a" then _n_ = 1 ;                                                                                                                                                                                                                                
    if _n_ then output ;                                                                                                                                                                                                                                        
  end ;                                                                                                                                                                                                                                                         
run ;                     

Kind regards

Paul D. 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 665 views
  • 5 likes
  • 3 in conversation