CODEWITHSUNDEEP
sas
Dakota Witzel
Dakota Witzel

Reputation: 13

Creating variables based on other variables in SAS

I'm looking to create a variable based on this data sample:

Video     Subject    Pre_post    Pre_Post_ID
  1          1          0             1
  1          2          0             1
  1          2          0             1
  1          3          0             1
  1          3          0             1
  2          1          1             1
  2          1          1             1
  2          2          1             1
  2          2          1             1
  2          3          1             1
  4          1          0             2
  4          2          0             2
  4          2          0             2
  4          3          0             2
  4          3          0             2
  5          1          1             2
  5          1          1             2
  5          2          1             2
  5          2          1             2
  5          3          1             2

The goal of the variable will be to create an ID that links the pre_post variable to the subject on the condition that the pre_post_id is the same:

Video     Subject    Pre_post    Pre_Post_ID   Subject_P_P_ID
  1          1          0             1               1
  1          2          0             1               2
  1          2          0             1               2
  1          3          0             1               3
  1          3          0             1               3
  2          1          1             1               1
  2          1          1             1               1
  2          2          1             1               2
  2          2          1             1               2
  2          3          1             1               3
  4          1          0             2               4
  4          2          0             2               5
  4          2          0             2               5
  4          3          0             2               6
  4          3          0             2               6
  5          1          1             2               4
  5          1          1             2               4
  5          2          1             2               5
  5          2          1             2               5
  5          3          1             2               6

Thank you in advance for the help!

Upvotes: 0

Views: 46

Answers (1)

Richard
Richard

Reputation: 27498

You will want to track the pairs (<pre_post_id>,<subject>) as a composite key and increment the Subject_P_P_ID every time a new pair (or key) is encountered.

To simplify the discussion, call the two items in the pair item1 and item2

Here are two ways:

  • Sort by item1 item2, step through BY item1 item2 and track pair count using logic based on an automatic first. variable -- pair_id + (first.item2), or
  • Track pairs as keys of a hash and assign new id as <hash>.num_items + 1 when key lookup fails.

Sort + Data Step + Revert Sort

proc sort data=have out=have_sorted;
  by item1 item2;
run;
data have_sequenced;
  set have_sorted;
  by item1 item2;
  item1_item2_pair_id + (first.item2);
run;
proc sort data=have_sequenced out=want;
  by video subject pre_post pre_post_id item1_item2_pair_id;
run;

Hash

data want;
  set have;
  if _n_=1 then do;
    declare hash lookup();
    lookup.defineKeys('item1', 'item2');
    lookup.defineData('item1_item2_pair_id');
    lookup.defineDone();
  end;
  if lookup.find() ne 0 then do;
    item1_item2_pair_id = lookup.num_items+1;
    lookup.add();
  end;
end;

Upvotes: 1

Related Questions