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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Reeza
Super User

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
PROC Star

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

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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