How to subgroup based on the _ criteria

[ybz12003]

Hello,

I would like to get the seperated subgroups Result1-Result3 from the column 'Name' when the numbers are separated by _.   Thanks.

data datain1;
      infile datalines dsd;
  input Name : $300.  Result1 : $100. Result2 : $100. Result3 : $100.;
datalines;
	5648_6666, 5648, 6666,
	000_9463, 000, 9463,
	000_4721_444, 000, 4721, 444
	4721_00_999, 4721, 00, 999
;
run;

[PaigeMiller]

You can use the SCAN() function along with the COUNTW() function, with the underscore as the delimiter, in a loop.

[ballardw]

I'm feeling generous. Here's one example of @PaigeMiller's suggestion.

Note that I am only reading your Name variable.

data datain1;
      infile datalines dsd;
  input Name : $300.  ;
  array result (3) $100;
  do i=1 to countw(name,'_');
   result[i] = scan(name,i,'_');
  end;
  drop i;
datalines;
	5648_6666, 5648, 6666,
	000_9463, 000, 9463,
	000_4721_444, 000, 4721, 444
	4721_00_999, 4721, 00, 999
;
run;

Caveat: To create the Array you really need to know the maximum number of values you might encounter. If you declare the array for 3 as in the example and one of the name values was 12_34_56_78 then Countw will return 4 words and attempt to loop to the 4th element of the result array. Which was not defined, only 3.

Also consecutive _ may not do exactly what you want. Test it and see with a value like 123__456 (that is two consecutive underscores). Another case to consider is behavior if the first or last character is an _.

[PaigeMiller]

---

@ballardw wrote:

To create the Array you really need to know the maximum number of values you might encounter.

---

That sounds like Maxim 3!