SAS: Making a new column from a value of rows and retaining those values through every unique request ID

Question

i have an example dataset here. enter image description here

i want to make a new column named approver by extracting the "to_user" values if the workflow status contains "approval". it should be corresponding to each unique request no. i give the example of what i expect for the new column. i am very new to sas, i did the functions research but still get stuck. thank you for helping!

Answer 1

You can use by-group processing to determine (compute) the last approver within the group, and then apply that to each row in the group.

Example:

Compute a value for a group and apply it to each row in the group using DOW loop processing.

data want;
  do _n_ = 1 by 1 until (last.request_no);
    set have;
    by request_no;
    if find(to_role,'approver','i') then approver = to_user;
  end;
  do _n_ = 1 to _n_;
    set have;
    output;
  end;
run;

Answer 2

Since you are very new to SAS, a SQL-based method can make this easy to understand. Create a subset table that gets the value of TO_USER when WORKFLOW_STATUS = 'APPROVAL_PINCAB'. This will create a table that looks like this:

REQUEST_NO             TO_USER
1401.23.039.1.00005-1  Syahruddin
1402.17.040.6.00023-2  Braindy Soekarno
1402.18.040.6.00072-1  Braindy Soekarno

Now if we do a left join on this table by REQUEST_NO, we'll populate the approver for all values of REQUEST_NO that match.

proc sql;
    create table want as
        select t1.*, t2.approver
        from have as t1
        LEFT JOIN
             (select request_no
                   , to_user as approver
              from have
              where workflow_status = 'APPROVAL_PINCAB'
             ) as t2
        ON t1.request_no = t2.request_no
     ;
quit;

Another way to do this would be through a data step merge. You can do this all in one step, but for clarity this is it in multiple steps.

data lookup;
    set have;
    where workflow_status = 'APPROVAL_PINCAB';
    
    keep request_no to_user;
    rename to_user = APPROVER;
run;

proc sort data=lookup;
    by request_no;
run;

proc sort data=have;
    by request_no;
run;

data want;
    merge have
          lookup
    ;
    by request_no;
run;

An advanced way of doing this is through something called a hash table (Kirk Paul Lafler). These are extremely efficient and are great for joining tables like this. I won't go into all the details of it as it's explained in the linked paper in the section "Search and lookup with a simple key," but as you advance in SAS you'll find hash tables to be incredibly useful.

The below code will store a table in memory that only holds values where WORKFLOW_STATUS = 'APPROVAL_PINCAB'. As the data step pulls rows in one by one, we'll compare the value of request_no to the value of request_no in memory using the Find() method. If the values match, we'll pull the value of approver from the hash table into the dataset.

data want;
    set have;

    /* On the first iteration, define and load a hash table once */
    if(_N_ = 1) then do;
        length approver $50.;

        /* Define the in-memory lookup table */
        dcl hash lookup(dataset: 'have(where=(workflow_status = "APPROVAL_PINCAB")
                                       rename=(to_user = approver)
                                      )'
                       );
            lookup.defineKey('request_no'); * Define the lookup key;
            lookup.defineData('approver');  * Define the data to pull;
        lookup.defineDone();                * Load the table;
   
        call missing(approver); * Needed to prevent uninitialized value notes;
    end;

    /* Compare the set table's value of request_no and compare it 
       to the hash table. If they match, pull the value of approver 
       from the hash table into the new table we're creating. */
    rc = lookup.Find();
    
    drop rc;
run;