I have the following dataset. I need a new column where the sort order remains unchanged, meaning sorted by name and visit. I need a new column where the first set of non-missing values is assigned the value 1, the first set of missing values is assigned the value 2, the second set of non-missing values is assigned the value 3, and so on. How can I achieve this in SAS

data test;
  input name $ visit $ aval;
  cards;
  A101 w1 10
  A101 w2 15
  A101 w3 18
  A101 w4  .
  A101 w5 45
  A101 w6 50
  A101 w7 .
  A101 w8 89
  A101 w9 20
  A101 w10 19
  ;
  run;

output need

data test1;
  input name $ visit $ aval seq;
  cards;
  A101 w1 10 1
  A101 w2 15 1
  A101 w3 18 1
  A101 w4  . 2
  A101 w5 45 3
  A101 w6 50 3
  A101 w . 4
  A101 w8 89 5
  A101 w9 20 5
  A101 w10 19 5
  ;
  run;

I have the following dataset. I need a new column where the sort order remains unchanged, meaning sorted by name and visit. I need a new column where the first set of non-missing values is assigned the value 1, the first set of missing values is assigned the value 2, the second set of non-missing values is assigned the value 3, and so on. How can I achieve this in SAS

data test;
  input name $ visit $ aval;
  cards;
  A101 w1 10
  A101 w2 15
  A101 w3 18
  A101 w4  .
  A101 w5 45
  A101 w6 50
  A101 w7 .
  A101 w8 89
  A101 w9 20
  A101 w10 19
  ;
  run;

output need

data test1;
  input name $ visit $ aval seq;
  cards;
  A101 w1 10 1
  A101 w2 15 1
  A101 w3 18 1
  A101 w4  . 2
  A101 w5 45 3
  A101 w6 50 3
  A101 w . 4
  A101 w8 89 5
  A101 w9 20 5
  A101 w10 19 5
  ;
  run;

• sas

Share Improve this question Follow edited Mar 31 at 15:37 Akhila Varghese asked Mar 31 at 15:33 Akhila VargheseAkhila Varghese 2133 bronze badges New contributor Akhila Varghese is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 1

• To make the question/subject/title clearer you should replace "based on specific condition" with words that describe the condition. – Tom Commented Mar 31 at 18:26

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

You could make use of the notsorted flag since it's already in a predefined order, then conditionally sum when:

1. It's the first observation in group

2. aval is missing or the previous value of aval is missing and it's not the first observation in the group

data want;
    set test;
    by name notsorted;

    lag_aval = lag(aval);

    if(first.name) then seq = 1;

    if( (aval = . OR lag_aval = .) AND NOT first.name)
        then seq+1
    ;
 
    drop lag_aval;
run;

name    visit   aval    seq
A101    w1  10  1
A101    w2  15  1
A101    w3  18  1
A101    w4  .   2
A101    w5  45  3
A101    w6  50  3
A101    w7  .   4
A101    w8  89  5
A101    w9  20  5
A101    w10 19  5

You can compute the sequence value for a band of aval values with the following

data want ;
  set have ;
  by name ;
  seq + (avail ne lag(avail)) ;
  if first.name then seq = 1 ;
run ;