topic Re: compare first and last observation in SAS Programming

compare first and last observation

MadMax — Wed, 15 Jul 2020 05:20:12 GMT

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

Re: compare first and last observation

Shmuel — Wed, 15 Jul 2020 06:04:25 GMT

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;

Re: compare first and last observation

MadMax — Wed, 15 Jul 2020 07:15:47 GMT

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"

Re: compare first and last observation

Kurt_Bremser — Wed, 15 Jul 2020 08:06:28 GMT

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

Re: compare first and last observation

Kurt_Bremser — Wed, 15 Jul 2020 08:11:46 GMT

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.

Re: compare first and last observation

Shmuel — Wed, 15 Jul 2020 09:39:27 GMT

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
****/

Re: compare first and last observation

MadMax — Wed, 15 Jul 2020 10:20:38 GMT

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.

Re: compare first and last observation

Kurt_Bremser — Wed, 15 Jul 2020 10:31:58 GMT

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.

Re: compare first and last observation

MadMax — Wed, 15 Jul 2020 11:01:11 GMT

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