Comparing values in groups

[lydiawawa]

Hi All,

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)

Any help will be appreciated!

ID	Name	Value	Start_ind	Switch_from_start
1	Start	1	1	no
1	apple	1
1	pear	1
2	Start	2	1	Yes, 2_1
2	apple	2
2	peach	2
2	pear	1
2	guava	1
2	banana	2
3	Start	1	1	Yes, 1_2
3	apple	2
3	peach	2
3	pear	1

[mkeintz]

What are the rules for generating switch_within and switch_from_start

[lydiawawa]

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. (see table for output example)

switch_within: combine the before and after values within the same ID if a change occurs. (see table for output example)

Are those what you are looking for?

[Reeza]

Is your 5th record in that table for switch_within correct? I'm not understanding why the ID=3, Value=2, Name=Apple has a switchwithin and record #5 does not.

[lydiawawa]

Hi, I removed switch_within because there is no way I can sort the records following Name = "Start" within the same ID. (I cannot sort Name alphabetically)

[Kurt_Bremser · Accepted solution]

Use a double DOW loop:

data have;
infile datalines truncover;
input ID $ Name $ value start_ind;
datalines; 
1 Start 1 1
1 apple 1 
1 pear 1
2 Start 2 1
2 apple 2
2 peach 2
2 pear 1
2 guava 1
2 banana 2
3 Start 1 1
3 apple 2
3 peach 2
3 pear 1
;

data want;
if 0 then set have; /* sets variable order */
length switch_from_start $10;
switch_from_start = 'no';
do until (last.id);
  set have;
  by id;
  if first.id
  then startval = value;
  else if startval ne value and switch_from_start = 'no'
  then switch_from_start = catx(' ','Yes,',cats(startval,'_',value));
end;
do until (last.id);
  set have;
  by id;
  output;
  switch_from_start = '';
end;
drop startval;
run;

proc print data=want noobs;
run;

Result:

                                    switch_
                          start_    from_
 ID     Name     value      ind     start

 1     Start       1         1      no      
 1     apple       1         .              
 1     pear        1         .              
 2     Start       2         1      Yes, 2_1
 2     apple       2         .              
 2     peach       2         .              
 2     pear        1         .              
 2     guava       1         .              
 2     banana      2         .              
 3     Start       1         1      Yes, 1_2
 3     apple       2         .              
 3     peach       2         .              
 3     pear        1         .              

[Kurt_Bremser]

PS note how I presented the example dataset in a data step with datalines. It allows everybody else to safely recreate your dataset exactly with just a simple copy/paste and submit. Please present data in that way in the future; it's not really complicated and speeds up finding a solution for you. Help us to help you. 

[lydiawawa]

Hi Kurt, I will be sure to do that next time. Thank you so much for the help.