Help using Base SAS procedures

Matching transaction and worked records to determine what was worked

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Matching transaction and worked records to determine what was worked

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:
ClassCust_IDtDatetTimeWorkedRemovedWorkedID
Class11234432111/6/11172904Workeda321
Class11234432111/6/11174232Workedab23
Class21234432111/7/1132521Removed
Class21234432111/7/11131519Workedv4
Class11234432111/7/11143025Removed
Class31234432111/7/11212314Workedf1
Class11234432111/7/11222424


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


Accepted Solutions
Solution
‎11-17-2011 09:04 PM
Super User
Posts: 10,035

Re: Matching transaction and worked records to determine what was worked

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

View solution in original post


All Replies
PROC Star
Posts: 7,480

Matching transaction and worked records to determine what was worked

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;

Contributor
Posts: 54

Matching transaction and worked records to determine what was worked

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.

PROC Star
Posts: 7,480

Re: Matching transaction and worked records to determine what was worked

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;

PROC Star
Posts: 7,480

Re: Matching transaction and worked records to determine what was worked

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;

Contributor
Posts: 54

Matching transaction and worked records to determine what was worked

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;

PROC Star
Posts: 7,480

Re: Matching transaction and worked records to determine what was worked

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;



Super User
Posts: 10,035

Re: Matching transaction and worked records to determine what was worked

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

Contributor
Posts: 54

Re: Matching transaction and worked records to determine what was worked

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.

Super User
Posts: 10,035

Re: Matching transaction and worked records to determine what was worked

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 ?

Solution
‎11-17-2011 09:04 PM
Super User
Posts: 10,035

Re: Matching transaction and worked records to determine what was worked

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 143 views
  • 6 likes
  • 3 in conversation