CODEWITHSUNDEEP
sas
lydias
lydias

Reputation: 841

SAS Comparing values in groups

In the following table I'm trying to create Switch_from_start :

start_ind: I created this value to secure when name = "Start" as the first item within the same ID after sorted in descending order.

Data df;
  set df;
if name = "Start" then start_ind = 1;
run;

proc sort data = df; by ID Name descending start_id; run; 
/*In the table below Name is not sorted*/

switch_from_start: any change in Value in comparing to the Value when name = "Start" within the same ID. So if a different Value within the same ID occurs, switch_from_start will be "yes" + start_value + later appeared Value that is different than the start Value. (refer to output example below)

 

enter image description here

Any help will be appreciated!

Upvotes: 0

Views: 102

Answers (1)

Richard
Richard

Reputation: 27498

You are applying a 'value computed over the group' to the first row in the group. This kind of data processing flow can be accomplished with a DOW loop for computation over the group followed by a serial loop over the group for application and output. The tracking of first occurring distinct values in the group can be tracked in a variety of ways including a maximally sized temporary array, a dynamically sized hash, or a delimited concatenation.

Example using delimited concatenation:

findw is used to check for a value previously concatenated (this is the tracking). The delimiters are underscore (_) which is your separator, and space () for the trailing spaces of the concatenation

do … until is the DOW loop and do … to _n_ is the serial loop. Notice how the computed value is cleared after being output in the first row of each group.

data have;
  do id = 1 to 10;
    do _n_ = 1 to 20 * ranuni(123);
      length name $20;
      if _n_ = 1 
        then name = 'Start';
        else name = scan ('apple pear guava peach orange cherry lemon lime', ceil(8*ranuni(123)));

      value = ceil(6*ranuni(123));
      if id = 5 then 
        value = 1;

      output;
    end;
  end;
run;

data want;
  do _n_ = 1 by 1 until (last.id);
    set have;
    by id;

    length group_computation $75;

    if not findw (group_computation, strip(value), '_ ') then
      group_computation = catx('_', group_computation, value);
  end;

  if index(group_computation, '_') = 0
    then group_computation = 'no';
    else group_computation = 'Yes, ' || group_computation;

  do _n_ = 1 to _n_;
    set have;
    output;
    if _n_ = 1 then group_computation = '';
  end;    
run;

Upvotes: 1

Related Questions