DATA Step, Macro, Functions and more

Select Variable By Sequence

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Select Variable By Sequence

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.


Accepted Solutions
Solution
‎08-30-2017 03:31 PM
Super User
Posts: 5,518

Re: Select Variable By Sequence

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.

View solution in original post


All Replies
Super User
Posts: 19,870

Re: Select Variable By Sequence

[ Edited ]

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.


 

Solution
‎08-30-2017 03:31 PM
Super User
Posts: 5,518

Re: Select Variable By Sequence

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.

Super User
Posts: 11,343

Re: Select Variable By Sequence

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 91 views
  • 0 likes
  • 4 in conversation