Select Variable By Sequence

[ruzvmun]

Hi all,

I have a transaction file that contains account #s. Each account # has multiple transactions identified by tran_code for any single day.

I am trying to select account #s that follow a sequence of changes on the tran code. for example, by the end of the day..tran_code should = 'cld', however prior to that it should have been either 'op' or 'sub'. This should all happen in one day.

Here is an example of what the data looks like:

Account#      TranDate   TranTime TranCode

12342            2Jul17       8:00am     sub

12342            2Jul17       9:00am     op

12342            2Jul17       10:00am     dl

12342            2Jul17       11:00am     cld

32132            2Jul17       6:00am      sub

32132            2Jul17       11:35am     op

32132            2Jul17       12:46am     cld

I could use a simple Where tran_code in(op.sub,cld) but that wont gaurantee that my results meet the criteria for a singe date as these changes can happen on different dates.

Can someone help or at least point to a resource that I can refer to for this coding.

Thanks all.

[Reeza]

That's not really clear. 

Can you show what you have and what you want as output?

---

@ruzvmun wrote:

Hi all,

I have a transaction file that contains account #s. Each account # has multiple transactions identified by tran_code for any single day.

I am trying to select account #s that follow a sequence of changes on the tran code. for example, by the end of the day..tran_code should = 'cld', however prior to that it should have been either 'op' or 'sub'. This should all happen in one day.

Here is an example of what the data looks like:

Account#      TranDate   TranTime TranCode

12342            2Jul17       8:00am     sub

12342            2Jul17       9:00am     op

12342            2Jul17       10:00am     dl

12342            2Jul17       11:00am     cld

32132            2Jul17       6:00am      sub

32132            2Jul17       11:35am     op

32132            2Jul17       12:46am     cld

I could use a simple Where tran_code in(op.sub,cld) but that wont gaurantee that my results meet the criteria for a singe date as these changes can happen on different dates.

Can someone help or at least point to a resource that I can refer to for this coding.

Thanks all.

---

[Astounding · Accepted solution]

Assuming that sorting is a possibility:

proc sort data=have;

by Account TranDate;

run;

data want;

found=0;

do until (last.TranDate);

   set have;

   by Account TranDate;

   if TranCode in ('op', 'sub') then found=1;

end;

if TranCode='cld' then found = found + 1;

do until (last.TranDate);

   set have;

   by Account TranDate;

   if found=2 then output;

end;

drop found;

run;

The top loop examines all observations, and determines whether the combination should be output.  The bottom loop reads the same observations, and outputs if appropriate.

[ballardw]

You should indicate what your output would look like.

If your TranDate and TranTime are actually date and time valued SAS variables (so they will sort correctly) you can identify the Account and date that you want with (assuming sorted by account trandate trantime) then this may get you started:

data want;
   set have;
   by account trandate trantime;
   Retain WantFlag;
   if first.trandate then WantFlag =.;
   if TranCode in ('sub','op') then WantFlag=1;
   if Last.trandate and TranCode='cld' and WantFlag then output;
run;