Conditional Sum Across Array

[A_SAS_Man]

I asked a question yesterday related to this here: https://communities.sas.com/t5/SAS-Programming/Finding-Last-Column-with-Value-and-Save-Column-Name-o...

What I am trying to do now is to conditionally sum across the same data set as I reference in my previous question:

data input;
 input id1 supply1 supply2 supply3 supply4;
 datalines;
1 20 28 .  .
2 30 30 30 30
3 20 28 28 .
4 20 28 .  .
5 30 30 28 28
6 30 .  .  .
7 30 28 30 30
8 20 30 .  .
9 10 28 28 28
10 10 28 30 .
 ;

And come up with a result like below, where we sum up all except the last populated column.

 data sum_across_all_except_last;
 input id1 max;
 datalines;
1 20
2 90
3 48
4 20
5 88
6 30
7 88
8 20
9 66
10 38
 ;

Thank you for any help.

EDIT: One clarification, if there is only one column with populated values I would like that column to still be summed. That is demonstrated in my results data set but was unclear in my description. Please let me know if you need any more clarification on this.

[Kurt_Bremser]

As soon as you have the preferred long structure, it's a simple data step using by-group processing:

data input;
 input id1 supply1 supply2 supply3 supply4;
 datalines;
1 20 28 .  .
2 30 30 30 30
3 20 28 28 .
4 20 28 .  .
5 30 30 28 28
6 30 .  .  .
7 30 28 30 30
8 20 30 .  .
9 10 28 28 28
10 10 28 30 .
 ;

proc transpose data=input out=trans (drop=_name_ where=(col1 ne .));
by id1;
var supply:;
run;

data want;
set trans;
by id1;
if first.id1
then max = col1;
else if not last.id1 then max + col1;
if last.id1;
drop col1;
run;

proc print data=want noobs;
run;

Result:

id1    max

  1     20
  2     90
  3     48
  4     20
  5     88
  6     30
  7     88
  8     20
  9     66
 10     38

[PeterClemmensen · Accepted solution]

Building on @novinosrins solution to your original thread..

data want2(drop=i);
set input;
array s(*)  supply4- supply1;
array t(*)  supply1- supply4;
max=dim(s)-whichn(coalesce(of s(*)),of s(*))+1;
var_name=vname(t(max));
do i=1 to max(1, max-1);
    summin=sum(summin, t[i]);
end;
run;

[Ksharp]

data input;
 input id1 supply1 supply2 supply3 supply4;
 datalines;
1 20 28 .  .
2 30 30 30 30
3 20 28 28 .
4 20 28 .  .
5 30 30 28 28
6 30 .  .  .
7 30 28 30 30
8 20 30 .  .
9 10 28 28 28
10 10 28 30 .
 ;
data want;
 set input;
 array x{*} supply:;
 sum=0;
 do i=1 to dim(x);
  if not missing(x{i}) then temp=x{i};
  sum+x{i};
 end;
 if n(of x{*})>1 then sum=sum(sum,-temp);
run;

[FreelanceReinh]

Yet another option (assuming that the sample data are representative of the real data, in particular, that there are no missings between non-missing values):

data want3(keep=id1 max);
set input;
array supply[4];
supply[max(n(of supply:),2)]=.;
max=sum(of supply:);
run;

[novinosrin]

Hi @A_SAS_Man  Sorry too late to the party, missed out the fun earlier. FWIW, my share of fun

data input;
 input id1 supply1 supply2 supply3 supply4;
 datalines;
1 20 28 .  .
2 30 30 30 30
3 20 28 28 .
4 20 28 .  .
5 30 30 28 28
6 30 .  .  .
7 30 28 30 30
8 20 30 .  .
9 10 28 28 28
10 10 28 30 .
 ;

data want;
set input;
array s(*)supply:;
array t(9999) _temporary_;
call missing(of t(*));
_n_=n(of s(*));
call pokelong(peekclong(addrlong(s(1)),max(_n_-1,1)*8),addrlong(t(1)));
sum=sum(of t(*));
keep id1 sum;
run;

[hashman]

@A_SAS_Man:

Methinks it's quite simple, actually:

data have ;                                                                                                                             
  input id supply1-supply4 ;                                                                                                            
  cards ;                                                                                                                               
 1 20 28  .  .                                                                                                                          
 2 30 30 30 30                                                                                                                          
 3 20 28 28  .                                                                                                                          
 4 20 28  .  .                                                                                                                          
 5 30 30 28 28                                                                                                                          
 6 30  .  .  .                                                                                                                          
 7 30 28 30 30                                                                                                                          
 8 20 30  .  .                                                                                                                          
 9 10 28 28 28                                                                                                                          
10 10 28 30  .                                                                                                                          
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data want ;                                                                                                                             
  set have ;                                                                                                                            
  array s [*] supply: ;                                                                                                                 
  do _n_ = 1 to dim (s) while (N (s [_n_])) ;                                                                                           
    summa = sum (summa, s [_n_]) ;                                                                                                      
  end ;                                                                                                                                 
  summa = summa - s [_n_-1] * (_n_ > 2) ;                                                                                               
run ;                                      

Kind regards

Paul D.