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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

10 REPLIES 10
art297
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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

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
Fluorite | Level 6

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

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

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 1031 views
  • 6 likes
  • 3 in conversation