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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

8 REPLIES 8
Shmuel
Garnet | Level 18

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
Calcite | Level 5

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
Garnet | Level 18

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
Super User

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
Calcite | Level 5

 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
Super User

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
Calcite | Level 5

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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
  • 8 replies
  • 2150 views
  • 0 likes
  • 3 in conversation