BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lydiawawa
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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         .              

View solution in original post

7 REPLIES 7
mkeintz
PROC Star

What are the rules for generating switch_within and switch_from_start

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
lydiawawa
Lapis Lazuli | Level 10

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
Super User
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
Lapis Lazuli | Level 10
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
Super User

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
Super User

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
Lapis Lazuli | Level 10
Hi Kurt, I will be sure to do that next time. Thank you so much for the help.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2035 views
  • 2 likes
  • 4 in conversation