topic Re: selective summation in SAS Programming

selective summation

PITERSON_X_TB — Fri, 15 Apr 2022 13:42:21 GMT

I want to sum 1s for a given key, but only the last ones, if there was a gap, i.e. there was 0, I do not want to sum. example below:

ID	val_202001	val_202002	val_202003	val_202004	val_202005	val_202006	val_202007	val_202008	val_202009	val_202010	val_202011	val_202012	val_202101	val_202102	val_202103	expected
1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	15
2	1	1	0	0	1	1	1	1	1	1	1	1	1	1	1	11
3	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	5

Thanks for your help / inspiration

Re: selective summation

PaigeMiller — Fri, 15 Apr 2022 14:00:25 GMT

UNTESTED CODE

data want;
    set have;
    array x val_202001--val_202103;
    expected=0;
    do i=dim(x) to 1 by -1;
         if x(i)=1 then expected=expected+1;
         else if x(i)=0 then leave;
    end;
    drop i;
run;

If you want tested code, please provide (a portion of) the data as SAS data step code, which you can type in yourself, or create it via these instructions.

Re: selective summation

Kurt_Bremser — Fri, 15 Apr 2022 13:59:36 GMT

Do not keep data in structure, it makes working with it more complicated.

So first, transpose:

proc transpose data=have out=long (rename=(_name_=period col1=value));
by id;
var val:;
run;

Then it is easy:

data want;
set long;
by id;
if first.id or value = 0
then expected = 0;
else expected + 1;
if last.id;
run;

Re: selective summation

novinosrin — Fri, 15 Apr 2022 14:04:47 GMT


data have;
input ID	val_202001	val_202002	val_202003	val_202004	val_202005	val_202006	val_202007	val_202008	val_202009	val_202010	val_202011	val_202012	val_202101	val_202102	val_202103 ;
cards;
1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
2	1	1	0	0	1	1	1	1	1	1	1	1	1	1	1
3	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1
;

data want;
  set have;
  array t(*) val_: ;
  want = dim(t) - findc(cats(of t(*)),'0','b');
run;

Re: selective summation

average_joe — Fri, 15 Apr 2022 14:46:05 GMT

Not nearly as intuitive as the previous solutions, but very pithy.

Re: selective summation

novinosrin — Fri, 15 Apr 2022 15:39:54 GMT

Thank you @average_joe

Re: selective summation

Astounding — Fri, 15 Apr 2022 19:03:54 GMT

Could your series end with 0? Could there be multiple groups, not just 1 or 2? What should the result look like for these series of values?

1	1	0	0	1	1	1	1	1	1	1	1	1	0	1
1	1	0	0	1	1	1	1	1	1	1	1	1	0	0

Re: selective summation

novinosrin — Fri, 15 Apr 2022 19:47:50 GMT

Sir @Astounding Nice one. Indeed a certain possibility that expects the logic to pick just the right most set of 1's if it were to exist -


data have;
input ID	val_202001	val_202002	val_202003	val_202004	val_202005	val_202006	val_202007	val_202008	val_202009	val_202010	val_202011	val_202012	val_202101	val_202102	val_202103 ;
cards;
1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
2	1	1	0	0	1	1	1	1	1	1	1	1	1	1	1
3	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1
4   1	1	0	0	1	1	1	1	1	1	1	1	1	0	1
5   1	1	0	0	1	1	1	1	1	1	1	1	1	0	0
6   0	0	0	0	0 	0 	0	0	0	0	0	0	0	0	0
;

data want;
  set have;
  array t(*) val_: ;
  want = lengthn(scan(cats(of t(*)),-1,'1','k'));
run;

Re: selective summation

Ksharp — Sat, 16 Apr 2022 11:09:56 GMT

data have;
input ID	val_202001	val_202002	val_202003	val_202004	val_202005	val_202006	val_202007	val_202008	val_202009	val_202010	val_202011	val_202012	val_202101	val_202102	val_202103 ;
cards;
1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
2	1	1	0	0	1	1	1	1	1	1	1	1	1	1	1
3	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1
4   1	1	0	0	1	1	1	1	1	1	1	1	1	0	1
5   1	1	0	0	1	1	1	1	1	1	1	1	1	0	0
6   0	0	0	0	0 	0 	0	0	0	0	0	0	0	0	0
;

data want;
  set have;
temp=cats(of val_:);
pid=prxparse('/1+/');
s=1;e=length(temp);
call prxnext(pid,s,e,temp,p,l);
do while(p>0);
 want=l;
 call prxnext(pid,s,e,temp,p,l);
end;
drop temp pid s e p l;
run;