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)
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 |
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 .
What are the rules for generating switch_within and switch_from_start
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?
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 .
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.