topic Re: How to subgroup based on the _ criteria in SAS Programming

How to subgroup based on the _ criteria

ybz12003 — Fri, 28 Feb 2020 20:29:04 GMT

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;

Re: How to subgroup based on the _ criteria

PaigeMiller — Fri, 28 Feb 2020 20:35:21 GMT

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

Re: How to subgroup based on the _ criteria

ballardw — Fri, 28 Feb 2020 21:01:45 GMT

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 _.

Re: How to subgroup based on the _ criteria

PaigeMiller — Fri, 28 Feb 2020 21:06:14 GMT

@ballardw wrote:

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

That sounds like Maxim 3!