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.
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;
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
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;
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;
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.