Matching transaction and worked records to determine what was worked

[Mishka1]

Hello, This requires a bit of explanation and an example so please hang in there. I'm using Base SAS 9.1

I work with a system where we work transactions from customers (cust_ID). Each transaction is given one or more classes to determine who will work it. The first transaction that is worked needs to be given a “worked” designation and the ID of the worked instance. When that transaction is worked, the rest of the transactions are removed from the system. The other transactions need to be designated as “Removed”. I need help figuring out how to do this. I tried using Proc SQL with creative groupings but I can’t figure out how to get it to “reset” with each new worked instance. Anyone know how to do this?

I have one data set with transactions and one with worked instances. See below.

DataSet: Transactions
Class	Cust_ID	tDate	tTime
Class1	12344321	11/6/11	172904
Class1	12344321	11/6/11	174232
Class1	12344321	11/7/11	32521
Class2	12344321	11/7/11	131519
Class1	12344321	11/7/11	143025
Class3	12344321	11/7/11	212314
Class1	12344321	11/7/11	222424
DataSet: Worked
Class	Cust_ID	tDate	tTime	WorkedID
Class1	12344321	11/6/11	173400	a321
Class1	12344321	11/6/11	191500	ab23
Class2	12344321	11/7/11	144800	v4
Class2	12344321	11/7/11	173200	v5
Class3	12344321	11/7/11	213800	f1
Illustration of what I want SAS to do.
*Added data is in yellow
dataset	Class	Cust_ID	tDate	tTime	WorkedRemoved	WorkedID
Trans	Class1	12344321	11/6/11	172904	Worked	a321	***below
Worked	Class1	12344321	11/6/11	173400		a321
Trans	Class1	12344321	11/6/11	174232	Worked	ab23
Worked	Class1	12344321	11/6/11	191500		ab23
Trans	Class2	12344321	11/7/11	32521	Removed
Trans	Class2	12344321	11/7/11	131519	Worked	v4
Trans	Class1	12344321	11/7/11	143025	Removed
Worked	Class2	12344321	11/7/11	144800		v4
Worked	Class2	12344321	11/7/11	173200		v5
Trans	Class3	12344321	11/7/11	212314	Worked	f1
Worked	Class3	12344321	11/7/11	213800		f1
Trans	Class1	12344321	11/7/11	222424
***If this is the most recent transaction in its class then find the next worked instance. If the Class name is the same, write "Worked" in WorkedRemoved and write the "WorkedID" of the worked event to the WorkID field. If the next worked instance has a different name, write "Removed" into the WorkedRemoved variable. If there is no worked instance after the queued, then don't write anything.

I want my trans table to look like this:
Class	Cust_ID	tDate	tTime	WorkedRemoved	WorkedID
Class1	12344321	11/6/11	172904	Worked	a321
Class1	12344321	11/6/11	174232	Worked	ab23
Class2	12344321	11/7/11	32521	Removed
Class2	12344321	11/7/11	131519	Worked	v4
Class1	12344321	11/7/11	143025	Removed
Class3	12344321	11/7/11	212314	Worked	f1
Class1	12344321	11/7/11	222424

Thank you for making it this far! Let me know if I need to clear anything else up.

[art297]

I don't understand the rules between the two stages (1) this is what I want SAS to do and (2) this is what I want my trans table to look like.  The following only accomplishes (1):

Data Trans;

  informat tDate mmddyy8.;

  format tDate mmddyy8.;

  input Class $ Cust_ID tDate tTime;

  cards;

Class1 12344321 11/6/11 172904

Class1 12344321 11/6/11 174232

Class1 12344321 11/7/11 32521

Class2 12344321 11/7/11 131519

Class1 12344321 11/7/11 143025

Class3 12344321 11/7/11 212314

Class1 12344321 11/7/11 222424

;

Data Worked;

  informat tDate mmddyy8.;

  format tDate mmddyy8.;

  input Class $ Cust_ID tDate tTime WorkedID $;

  cards;

Class1 12344321 11/6/11 173400 a321

Class1 12344321 11/6/11 191500 ab23

Class2 12344321 11/7/11 144800 v4

Class2 12344321 11/7/11 173200 v5

Class3 12344321 11/7/11 213800 f1

;

data want;

  set Trans Worked INDSNAME=ds;

  dataset=scan(ds,-1,'.');

run;

proc sort data=want;

  by Cust_ID tDate tTime Class;

run;

[Mishka1]

Sorry Art. 1 is just a visual representation to help explain the rules that I want SAS to execute.

What I need is for the WorkedRemoved and WorkedID field to be added to the Trans dataset.

The meat and potatoes is this:
Evaluate a transaction.

***If this is the most recent transaction in its class then find the next worked instance. If the Class name is the same, write "Worked" in WorkedRemoved and write the "WorkedID" of the worked event to the WorkID field. If the next worked instance has a different class name or this is not the most current trans in this class, write "Removed" into the WorkedRemoved variable. If there is no worked instance after this trans, then don't write anything.

If this still doesn't make sense please let me know and I'll take a step back and try it from a different angle. I've been working on this for so long I may just need a different perspective to make it make sense.

Also, I'm not sure if 9.1 knows how to use the INDSNAME. It looks handy though. I'm getting an error when running. It doesn't like the Option name.

[art297]

Didn't know you were on 9.1.  The following should work on 9.1  Still only does the first part though:

data want;

  set Trans (in=int) Worked (in=inw) ;

  length dataset $6;

  if int then dataset="trans";

  else if inw then dataset="worked";

run;

proc sort data=want;

  by Cust_ID tDate tTime Class;

run;

[art297]

I think that the following accomplishes everything that you specified:

Data Trans;

  informat tDate mmddyy8.;

  format tDate mmddyy8.;

  input Class $ Cust_ID tDate tTime;

  cards;

Class1 12344321 11/6/11 172904

Class1 12344321 11/6/11 174232

Class1 12344321 11/7/11 32521

Class2 12344321 11/7/11 131519

Class1 12344321 11/7/11 143025

Class3 12344321 11/7/11 212314

Class1 12344321 11/7/11 222424

;

Data Worked;

  informat tDate mmddyy8.;

  format tDate mmddyy8.;

  input Class $ Cust_ID tDate tTime WorkedID $;

  cards;

Class1 12344321 11/6/11 173400 a321

Class1 12344321 11/6/11 191500 ab23

Class2 12344321 11/7/11 144800 v4

Class2 12344321 11/7/11 173200 v5

Class3 12344321 11/7/11 213800 f1

;

data temp;

  set Trans (in=int) Worked (in=inw);

  length dataset $6;

  if int then dataset="trans";

  else if inw then dataset="worked";

run;

proc sort data=temp;

  by Cust_ID Class tDate tTime;

run;

data want (drop=next_: dataset);

  set temp;

  by Cust_ID Class tDate;

  set temp ( firstobs = 2

             keep = dataset WorkedID

             rename = (dataset= next_dataset

                       WorkedID=Next_WorkedID) )

      temp ( obs = 1 drop = _all_ );

  length WorkedRemoved $7;

  next_dataset = ifc(  last.tDate, (.), next_dataset );

  next_WorkedID = ifc(  last.tDate, (.), next_WorkedID );

  if strip(dataset) eq "trans" then do;

    if next_dataset eq "worked" then do;

      WorkedRemoved="Worked";

      WorkedID=next_WorkedID;

    end;

    else do;

      WorkedRemoved="Removed";

    end;

    output;

  end;

run;

[Mishka1]

Thanks Art. I always learn a lot from your solutions.

I'm trying to add in the stipulation that if there is no worked record after the trans that the WorkedRemoved and WorkedID will be null. I've tried the below but it isn't working. Do you see my mistake? I added what is in bold below. I tried it with telling the variables to be null as well and no luck there either.

data want (drop=next_: dataset);

  set temp;

  by Cust_ID Class tDate;

  set temp ( firstobs = 2

             keep = dataset WorkedID

             rename = (dataset= next_dataset

                       WorkedID=Next_WorkedID) )

      temp ( obs = 1 drop = _all_ );

  length WorkedRemoved $7;

  next_dataset = ifc(  last.tDate, (.), next_dataset, 1 ); *Added the 1 for if it's missing;

  next_WorkedID = ifc(  last.tDate, (.), next_WorkedID );

      if strip(dataset) eq "trans" then

      do;

      if next_dataset eq "worked" then

                  do;

                 WorkedRemoved="Worked";

                        WorkedID=next_WorkedID;

            end;

                  else

                        if next_dataset eq 1 then 

                  do;                    

                  end;

            else do;

                        WorkedRemoved="Removed";

            end;

      output;

      end;

run;

[art297]

Next_dataset and dataset are character variables thus, when you used "if next_dataset eq 1" you should have gotten an error.  You either want to change it to:

if next_dataset eq "worked" then do;

or

if next_dataset eq "trans" then do;

[Ksharp]

Illustration of what I want SAS to do.

The fifth obs should be class1 not class2.

Data Trans;
  informat tDate mmddyy8.;
  format tDate mmddyy8.;
  input Class $ Cust_ID tDate  tTime;
  cards;
Class1 12344321 11/6/11 172904
Class1 12344321 11/6/11 174232
Class1 12344321 11/7/11 32521
Class2 12344321 11/7/11 131519
Class1 12344321 11/7/11 143025
Class3 12344321 11/7/11 212314
Class1 12344321 11/7/11 222424
;
run;

 

Data Worked;
  informat tDate mmddyy8.;
  format tDate mmddyy8.;
  input Class $ Cust_ID tDate tTime WorkedID $;
  cards;
Class1 12344321 11/6/11 173400 a321
Class1 12344321 11/6/11 191500 ab23
Class2 12344321 11/7/11 144800 v4
Class2 12344321 11/7/11 173200 v5
Class3 12344321 11/7/11 213800 f1
;
run;


data want(drop=_: found rc);
 if _n_ eq 1 then do;
  if 0 then set worked(rename=(class=_class cust_id=_cust_id tdate=_tdate ttime=_ttime));
  declare hash ha(hashexp:10,dataset:'worked(rename=(class=_class cust_id=_cust_id tdate=_tdate ttime=_ttime))',ordered:'A');
  declare hiter hi('ha');
   ha.definekey('_tdate','_ttime');
   ha.definedata('_tdate','_ttime','_class','_cust_id','workedid');
   ha.definedone();
 end;
set trans;
length WorkedRemoved $ 10;
_rc=hi.first();
do while(_rc=0);
 if class=_class and cust_id=_cust_id then do;
                                             found=1;
                                             WorkedRemoved='worked';
                                             _k1=_tdate;
                                             _k2=_ttime;
                                             output;
                                           end;
 _rc=hi.next();
 if found then do; rc=ha.remove(key:_k1,key:_k2); leave;end;
 end;
 if not found and (tdate lt _tdate or (tdate eq _tdate and ttime lt _ttime)) then do;
                                          WorkedRemoved='removed';
                                          call missing(workedid);
                                          output;
                                         end;
  else if not found and (tdate ge _tdate or (tdate eq _tdate and ttime ge _ttime)) then do;
                                   call missing(WorkedRemoved,workedid);
                                   output;
                                 end;
run;

Ksharp

[Mishka1]

Thanks Ksharp,

I'm getting an error on the line

ha.definedone();

"Uninitialized keys for hash object at line 3039 column 4"

Do I need an argument in there?

If I comment that line out, it runs, but the output has null values for all of the WorkedRemoved and WorkedID fields.

[Ksharp]

It is so weird. I run my code again, there is no error generated.

Did you run my whole code , include datalines?

What  is version of your SAS ?

[Ksharp · Accepted solution]

Not sense you are using 9.1.

The following code should be running. I tested it at SAS 9.1.3. NOTE: worked has different variables names

Data Trans;
  informat tDate mmddyy8.;
  format tDate mmddyy8.;
  input Class $ Cust_ID tDate  tTime;
  cards;
Class1 12344321 11/6/11 172904
Class1 12344321 11/6/11 174232
Class1 12344321 11/7/11 32521
Class2 12344321 11/7/11 131519
Class1 12344321 11/7/11 143025
Class3 12344321 11/7/11 212314
Class1 12344321 11/7/11 222424
;
run;

 

Data Worked;
  informat _tDate mmddyy8.;
  format _tDate mmddyy8.;
  input _Class $ _Cust_ID _tDate _tTime WorkedID $;
  cards;
Class1 12344321 11/6/11 173400 a321
Class1 12344321 11/6/11 191500 ab23
Class2 12344321 11/7/11 144800 v4
Class2 12344321 11/7/11 173200 v5
Class3 12344321 11/7/11 213800 f1
;
run;


data want(drop=_: found rc);
 if _n_ eq 1 then do;
  if 0 then set worked;
  declare hash ha(hashexp:10,dataset:'worked',ordered:'A');
  declare hiter hi('ha');
   ha.definekey('_tdate','_ttime');
   ha.definedata('_tdate','_ttime','_class','_cust_id','workedid');
   ha.definedone();
 end;
set trans;
length WorkedRemoved $ 10;
_rc=hi.first();
do while(_rc=0);
 if class=_class and cust_id=_cust_id then do;
                                             found=1;
                                             WorkedRemoved='worked';
                                             _k1=_tdate;
                                             _k2=_ttime;
                                             output;
                                           end;
 _rc=hi.next();
 if found then do; rc=ha.remove(key:_k1,key:_k2); leave;end;
 end;
 if not found and (tdate lt _tdate or (tdate eq _tdate and ttime lt _ttime)) then do;
                                          WorkedRemoved='removed';
                                          call missing(workedid);
                                          output;
                                         end;
  else if not found and (tdate ge _tdate or (tdate eq _tdate and ttime ge _ttime)) then do;
                                   call missing(WorkedRemoved,workedid);
                                   output;
                                 end;
run;

Ksharp