compare first and last observation

[MadMax]

Good Day, 

I have a problem which I am sure there is a simple solution to.  I have a dataset with  2 rows per customer 

Account       Month               Flag

CustA           April                  C

CustA           May                  C 

CustB           Nov                   C

CustB           Dec                   O

In the above example what I am lloking for is writing code to basically say: 

If your first observation for the customer is "C" and your last is also "C" then  indicator = "PASS"

but if your first observation of the flag is "C" and your last observation is "O" then your indicator = "FAIL" 

So the result should look like this 

Account       Month               Flag        Indicator

CustA           April                  C            PASS

CustA           May                  C            PASS

CustB           Nov                   C            FAIL

CustB           Dec                   O            FAIL

[Shmuel]

1) Can it be a customer with only one observation? or more than 2 observation?
    In such case next code need adaption.

2) I assume that ACCOUNT is the customer ID in your data, and that the dataset

    is already sorted by Accout & Month, then:

data temp;
 set have;
      by account;
           retain first_flag;
           if first.account then first_flag = flag;
           else do;
                   if first_flag = 'C' and flag='C' then indicator='PASS;
                   else indicator = 'FAIL';
                  output;
          end;
          keep account indicator;
run;
data want;
merge have indicator;
  by account;
run;

[MadMax]

Thank you Shmuel, 

I really appreciate your time. 

For some reason all of the indicators come back as "FAIL" . 

The data is already sorted by account and month's Descending. 

If the first.observation of the account's flag is "C" which means closed, and the previous month observation (last observation) is open, it means it should FAIL as you cant have a closed invoice this month if last month is still open. 

If this month is closed , last month should also be closed (PASS) 

even is this month is "O" (Open) last month may be "C" (Closed) this too is a "PASS"

[Shmuel]

Next is a tested code:

data have;
   input Account $ Month $ Flag  $;
cards;
CustA April C           
CustA May   C             
CustB Nov   C             
CustB Dec   O        
; run;
data temp;
 set have;
      by account;
           retain first_flag;
           if first.account then first_flag = flag;
           else do;
                   if first_flag = 'C' and flag='C' then indicator='PASS';
                   else indicator = 'FAIL';
                  output;
          end;
          keep account indicator;
run;
data want;
merge have temp;
  by account;
run;    
/**** result:

Obs	Account	Month	Flag	indicator
1	CustA	April	C	PASS
2	CustA	May	C	PASS
3	CustB	Nov	C	FAIL
4	CustB	Dec	O	FAIL
****/

[Kurt_Bremser]

Do you only need to compare for sameness, or  is it necessary that both first and last observations have the specific value "C"?

[Kurt_Bremser · Accepted solution]

Anyway, use a double DO loop:

data want;
do until (last.account);
  set have;
  by account;
  if first.account then _flag = flag;
end;
if flag = _flag
then indicator = 'PASS';
else indicator = 'FAIL';
do until (last.account);
  set have;
  by account;
  output;
end;
drop _flag;
run;

You may need to adapt the condition in the IF.

[MadMax]

 Thank you so much! 

I have made a small change to your code but it is something you could not have catered for as you had limited view of the data I work with.  It is working perfectly now. 

[Kurt_Bremser]

The nice feature of the double DO loop is the fact that, physically, it requires only one reading pass through the input dataset, so it will usually be faster than a SQL with auto-remerge, or a sequence of steps with a merge-back.

[MadMax]

That is very valuable to know. Thank you once again. Really appreciate your help!