topic Re: How to sum a string variable until you reach a certain character in SAS Programming

How to sum a string variable until you reach a certain character

silversta — Mon, 18 May 2020 18:32:09 GMT

Hello!

I am attempting to create a variable in my data that is the sum of the characters in a string (comprised of 0s and 1s). The code below does sum the 1s in the string, but I'm not sure how to manipulate it to provide the information that I'm looking for. For those whose strings start with 1s and change to 0s, that is ok. For those whose strings start with 0, regardless of the 1s that may follow, those need to be set to 0. Lastly, for those whose strings have 0s in the middle of the 1s, I need to only sum the 1s before it hits the 0. I apologize if this seems confusing and have tried to provide an illustration below of the string and the sum that I would like to calculate. Any help would be most appreciated!

x_all sum that code below calculates sum that I want

1111111000000 6 6

0000001111111 7 0

1111000111111 10 4

data want (drop = i );
set have;
x_total=0;
do i=1 to length(x_all);
x_total+input(substr(x_all,i,1),1.);
end;

Re: How to sum a string variable until you reach a certain character

Kurt_Bremser — Mon, 18 May 2020 18:44:35 GMT

Use a do while:

data want (drop = i );
set have;
x_total = 0;
i = 1;
do while (substr(x_all,i,1) = '1' and i le length(x_all));
  x_total + 1;
  i + 1;
end;
run;

Re: How to sum a string variable until you reach a certain character

novinosrin — Mon, 18 May 2020 18:47:47 GMT

HI @silversta If I understand you correctly, it's a simple boolean expression.


data have;
input x_all :$20.;*                                 sum that code below calculates        sum that I want;
cards;
1111111000000                            6                                                   6                     
0000001111111                            7                                                   0           
1111000111111
;

data want;
 set have;
 want=(verify(x_all,'1')>1)*(verify(x_all,'1')-1);
run;

Re: How to sum a string variable until you reach a certain character

Reeza — Mon, 18 May 2020 18:56:48 GMT

Seems to me this logic simplifies to finding the first 0.

If it's the first character you get a 0. If it's not the first 0, then you subtract 1 to find the position of the last 1 which is the sum of the values up to that point.

If you find no 0's, that means you have all ones, which is the length of the string.

data want;
set have;

x_total = findc(x_all, '0');

if x_total = 0 then x_total = length(x_all);
else if x_total > 0 then x_total - 1;

run;

Re: How to sum a string variable until you reach a certain character

Tom — Mon, 18 May 2020 19:12:45 GMT

Do you want to sum the digits or just count them?

You can use the VERIFY() function to find the first location that is not a '1'. Make sure to append something in case all of string is filled with ones. NOTE: Your first example as 7 ones, not six.

data test;
  input str :$20. wrong right ;
  want = verify(str||'0','1')-1 ;
cards;
1111111000000   6  6
0000001111111   7  0
1111000111111  10  4
;

Obs         str         wrong    right    want

 1     1111111000000       6       6        7
 2     0000001111111       7       0        0
 3     1111000111111      10       4        4

Re: How to sum a string variable until you reach a certain character

biopharma — Mon, 18 May 2020 22:29:16 GMT

Hi @novinosrin,

Nice solution.

Wondering whether this would be sufficient?

want = verify(x_all,'1')-1 ;

Re: How to sum a string variable until you reach a certain character

novinosrin — Mon, 18 May 2020 22:43:35 GMT

Yes indeed. Good catch. I'll have to be a more diligent like you are. Thank you!

Re: How to sum a string variable until you reach a certain character

novinosrin — Mon, 18 May 2020 23:14:33 GMT

Hi @biopharma I just showed my mother(a retired banker) how elegantly you made it more terse. However she thinks it's better to have boolean 1 capture in case of missings and value not padded with blank i.e if incoming data is equivalent to something like stripped(x_all) a missing value the risk of having -1 in the result is apparent, albeit I'd still vote opposing my mom's thoughts for the reason that occurence is less likely.

I can't generally win against her if there's even a remote chance, so it appears the following is what she means. lol

data have;
input x_all :$20.;*                                 sum that code below calculates        sum that I want;
cards;
1111111000000                            6                                                   6                     
0000001111111                            7                                                   0           
1111000111111
.
;

data want;
 set have;
 want=(verify(strip(x_all),'1')-1);
 want1=(verify(strip(x_all),'1')>1)*(verify(strip(x_all),'1')-1);
run;

Fun stuff:)

Re: How to sum a string variable until you reach a certain character

silversta — Mon, 18 May 2020 23:18:17 GMT

Thank you all for your responses, this was most helpful!

Re: How to sum a string variable until you reach a certain character

silversta — Mon, 18 May 2020 23:18:48 GMT

Worked like a charm, thank you!

Re: How to sum a string variable until you reach a certain character

Tom — Mon, 18 May 2020 23:36:09 GMT

In general with VERIFY() you have to control for the case when the value being passed is completely filled with valid values. In that case the result returned will be zero, not the length of the string. So when you are using it to to find the last valid value, like in this case, you can just append some invalid character to the end of the string you pass it. As long are your input string is not exactly 32,767 bytes long it should do what you want.

want = verify(x_all || ' ','1')-1 ;

Re: How to sum a string variable until you reach a certain character

novinosrin — Mon, 18 May 2020 23:37:28 GMT

I agree. It's a very tricky function yet a very classy one.

Re: How to sum a string variable until you reach a certain character

yabwon — Tue, 19 May 2020 12:29:51 GMT

Hi,

Just for fun:

data have;
input x_all :$20.; 
cards;
1111111000000
0000001111111
1111000111111
;
run;

data want;
 set have;
 want = lengthn(prxchange('s/^(1*)(0*)(.*)/$1/', -1, x_all));
run;

Bart

Re: How to sum a string variable until you reach a certain character

yabwon — Tue, 19 May 2020 13:13:06 GMT

...and one more:

data want1;
 set have;
 want = lengthn(scan(x_all,1,"0","M"));
run;

Bart

Re: How to sum a string variable until you reach a certain character

novinosrin — Tue, 19 May 2020 13:28:48 GMT

Thank you @yabwon for your time and contribution. Much appreciate it.

This one too perhaps?

  want=max(0,findc(x_all,'0')-1);

Re: How to sum a string variable until you reach a certain character

silversta — Tue, 19 May 2020 13:41:45 GMT

Hello @novinosrin,

Thanks again for the code, it was most helpful! Is there a way to modify it to sum through a string of 1 or 2 0s in the middle. I was told that I would have to count the 0s in the middle of the string as if it were a 1, as long as there are no more than 2 of them in the middle.

For example:

11110000011 = 4

111001111 =9

111001111000 =9 (would ignore the 0s at the end but count the 0s in the middle as part of the sum)

Re: How to sum a string variable until you reach a certain character

yabwon — Tue, 19 May 2020 13:52:11 GMT

Hi, try this:

data have;
input x_all :$20. expected ; 
cards;
11110000011 4
111001111 9
111001111000 9 
;
run;

data want;
 set have;
 want = lengthn(prxchange('s/^((1*)0{0,2}(1+))(.*)/$1/', -1, strip(x_all)));
run; 
proc print data=want;
run;

Bart

Re: How to sum a string variable until you reach a certain character

novinosrin — Tue, 19 May 2020 13:54:16 GMT

Hi @silversta

Can you please explain a bit more clearly how you got this result

11110000011 = 4

111001111 =9

111001111000 =9

Would the above mean the following?

11101111 =8

based upon "Is there a way to modify it to sum through a string of 1 or 2 0s in the middle. I was told that I would have to count the 0s in the middle of the string as if it were a 1, as long as there are no more than 2 of them in the middle. "

Re: How to sum a string variable until you reach a certain character

biopharma — Tue, 19 May 2020 14:04:08 GMT

Your Mom rocks! However remote the possibility, it may still be a good idea to program defensively.

Re: How to sum a string variable until you reach a certain character

novinosrin — Tue, 19 May 2020 14:04:09 GMT

Addendum-

@yabwon has answered your additional requirement even before I could begin