CODEWITHSUNDEEP
sas
sdhaoui
sdhaoui

Reputation: 369

SAS: get the first value where a condition is verified by group

I have this database: 

data temp;
input ID date type ;
  datalines;
 1 10/11/2006   1      
 1 10/12/2006   2      
 1 15/01/2007   2      
 1 20/01/2007   3    
 2 10/08/2008   1        
 2 11/09/2008   1        
 2 17/10/2008   1        
 2 12/11/2008   2    
 2 10/12/2008   3       
 ;

I would like to create a new column where I put the first date where the variable type changes from 1 to 2 by ID as follows:

data temp;
input ID date type  date_change_type1to2;
  datalines;
 1 10/11/2006   1        .
 1 10/12/2006   2        10/12/2006
 1 15/01/2007   2        .
 1 20/01/2007   3        .
 2 10/08/2008   1        .
 2 11/09/2008   1        .
 2 17/10/2008   1        .
 2 12/11/2008   2        12/11/2008
 2 10/12/2008   3        .
 ;

I have tried this code but it doesn't work:

  data temp;
  set temp;
  if first.type= 2 then date_change_type1to2=date;
  by ID;
  run;

Thank you in advance for your help!

Upvotes: 1

Views: 1001

Answers (2)

Llex
Llex

Reputation: 1770

Solution(input data must be sorted!):

data temp;
input ID date $10. type ;
  datalines;
 1 10/11/2006   1      
 1 10/12/2006   2      
 1 15/01/2007   2      
 1 20/01/2007   2     
 2 10/08/2008   1        
 2 11/09/2008   1        
 2 17/10/2008   1        
 2 12/11/2008   2    
 2 10/12/2008   2       
 ;
 run;

  data temp(drop=type_store);
     set temp;
     by ID;
     retain type_store;
     if first.id then type_store = type;
     if type ne type_store and type = 2 then do;
      date_change_type1to2=date;
      type_store = type;
     end;
  run;

Output:

+----+------------+------+----------------------+
| ID | date       | type | date_change_type1to2 |
+----+------------+------+----------------------+
| 1  | 10/11/2006 | 1    |                      |
+----+------------+------+----------------------+
| 1  | 10/12/2006 | 2    | 10/12/2006           |
+----+------------+------+----------------------+
| 1  | 15/01/2007 | 2    |                      |
+----+------------+------+----------------------+
| 1  | 20/01/2007 | 2    |                      |
+----+------------+------+----------------------+
| 2  | 10/08/2008 | 1    |                      |
+----+------------+------+----------------------+
| 2  | 11/09/2008 | 1    |                      |
+----+------------+------+----------------------+
| 2  | 17/10/2008 | 1    |                      |
+----+------------+------+----------------------+
| 2  | 12/11/2008 | 2    | 12/11/2008           |
+----+------------+------+----------------------+
| 2  | 10/12/2008 | 2    |                      |
+----+------------+------+----------------------+

Upvotes: 1

Tom
Tom

Reputation: 51621

The variable first.type will not be created if you have not included type in a by statement. And even if it did exits its value could never be 2, its value will be either 1 (true) or 0 (false).

If you just want to set it and keep its value for the rest of the observations for that ID then you could RETAIN the value. Make sure to clear it when starting a new ID value.

data temp;
  set temp;
  by ID;
  if first.id then date_change_type1to2=.;
  retain date_change_type1to2 ;
  if type=2 and missing(date_change_type1to2) then date_change_type1to2=date;
run;

Upvotes: 1

Related Questions