topic Re: Sum ifs in New SAS User

Sum ifs

Alexjaco1 — Wed, 05 Dec 2018 15:11:21 GMT

Can someone please help me write code that says if the grouper field is populated then Sum paid amount based on ID number.

Here's the data set that I have.

Data Have

OBS	ID	Grouper	Paid
1	111		100
2	111	0	100
3	111		100
4	122		100
5	122	7	100
6	123	1	100
7	125		100
8	125		100
9	125		100
10	125		100

Data Want

OBS	ID	Grouper	Paid
1	111	0	300
2	122	7	200
3	123	1	100
4	125		100
5	125		100
6	125		100
7	125		100

Re: Sum ifs

ballardw — Wed, 05 Dec 2018 15:24:17 GMT

@Alexjaco1 wrote:

Can someone please help me write code that says if the grouper field is populated then Sum paid amount based on ID number.

Here's the data set that I have.

Data Have

OBS ID Grouper Paid

1 111 100

2 111 0 100

3 111 100

4 122 100

5 122 7 100

6 123 1 100

7 125 100

8 125 100

9 125 100

10 125 100

Data Want

OBS ID Grouper Paid

1 111 0 300

2 122 7 200

3 123 1 100

4 125 100

5 125 100

6 125 100

7 125 100

If your requirement is "sum if grouper field is populated " how does your want have anything for ID 125 since none of the example data in either example has any value for the "grouper" variable? Your rule needs some additional clarification.

Re: Sum ifs

Alexjaco1 — Wed, 05 Dec 2018 15:27:15 GMT

Sum if grouper field is greater than or equal to 0, If grouper field is missing then keep that ID as is i.e. not rolled up to the ID Var.

Re: Sum ifs

novinosrin — Wed, 05 Dec 2018 15:38:33 GMT

data have;
input OBS 	ID	Grouper	Paid;
drop obs;
cards;
1	111	 .	100
2	111	0	100
3	111	 .	100
4	122	 .	100
5	122	7	100
6	123	1	100
7	125	 .	100
8	125	 .	100
9	125	 .	100
10	125	 .	100
;

data want;
do until(last.id);
set have;
by id;
if Grouper ne . then do;
_k=1;
_g=Grouper;
end;
end;
do until(last.id);
set have;
by id;
if _k then sum=sum(paid,sum);
else do; sum=paid;output;end;
end;
if _k then do;
Grouper=_g;
output;
end;
drop _: paid;
run;

Re: Sum ifs

Jagadishkatam — Wed, 05 Dec 2018 15:42:13 GMT

proc sort data=have;
by id descending grouper ;
run;

data want(keep=id grouper2 paid2 rename=(grouper2=grouper paid2=paid));
set have;
by id descending grouper ;
retain grouper2 paid2;
if first.id then grouper2=.;
if grouper ne . then grouper2=grouper;
if first.id  then paid2=.;
if grouper2 ne . then paid2+paid;
if paid2 eq . then paid2=paid;
if last.id and grouper2 ne . then output;
if grouper2 eq . then output;
run;

Re: Sum ifs

novinosrin — Wed, 05 Dec 2018 15:51:09 GMT

data have;
input OBS 	ID	Grouper	Paid;
drop obs;
cards;
1	111	 .	100
2	111	0	100
3	111	 .	100
4	122	 .	100
5	122	7	100
6	123	1	100
7	125	 .	100
8	125	 .	100
9	125	 .	100
10	125	 .	100
;
data want;
do until(last.id);
merge have have(keep=id grouper where=(grouper ne .) in=in2);
by id;
if in2 then do; 
sum=sum(paid,sum);
if Grouper>. then _g=Grouper;
end;
else  do; sum=paid;output;end;
end;
if in2 then  do;
Grouper=_g;
output;
end;
drop _:;
run;

Re: Sum ifs

Alexjaco1 — Wed, 05 Dec 2018 16:01:41 GMT

How would this code alter if I have a second and third Variable that needs to be summed up with Paid amount, let's call them Var2 and Var 3.

Re: Sum ifs

novinosrin — Wed, 05 Dec 2018 16:08:43 GMT

Please do me a favor. Post a good representative of what you HAVE and your WANT. Take your time, get the requirements in full and I will give you the solution in full. Thank you!

Re: Sum ifs

Alexjaco1 — Wed, 05 Dec 2018 16:10:28 GMT

I Apologize, I thought I could figure it out on my own...

Re: Sum ifs

novinosrin — Wed, 05 Dec 2018 16:19:22 GMT

Hey @Alexjaco1 Nothing to be apologize or formal about. I suggested the above just to save each other time. That's all

EDIT: Plus the fact, I consider all this as playing video game and so I am not a person who fits into advisory. Pardon me.

Re: Sum ifs

Alexjaco1 — Wed, 05 Dec 2018 16:36:40 GMT

Well, if you are still up to the challenge here is what I have

OBS	ID	Grouper	Paid	VAR 2	VAR 3
1	111		100	150	200
2	111	0	100	150	200
3	111		100	150	200
4	122		100	150	200
5	122	7	100	150	200
6	123	1	100	150	200
7	125		100	150	200
8	125		100	150	200
9	125		100	150	200
10	125		100	150	200

Here is what I want

OBS	ID	Grouper	Paid	VAR 2	VAR 3
1	111	0	300	450	600
2	122	7	200	300	400
3	123	1	100	150	200
4	125		100	150	200
5	125		100	150	200
6	125		100	150	200
7	125		100	150	200

Re: Sum ifs

novinosrin — Wed, 05 Dec 2018 16:37:55 GMT

is that final one? lol 🙂

Re: Sum ifs

Alexjaco1 — Wed, 05 Dec 2018 16:39:09 GMT

Yes sir.

Re: Sum ifs

novinosrin — Wed, 05 Dec 2018 16:41:39 GMT

Brb sir. and thank you. Need caffeine and will jump back from starbucks

Re: Sum ifs

Reeza — Wed, 05 Dec 2018 16:57:45 GMT

Here's a simplified approach using SQL.

First generate a list of ID's that have a grouper value available. Note this assumes you'll have only a single value for grouper per ID. If that can vary, it needs to be modified.

1. Generate list of ID with grouper value

2. For list of ID from #1, summarize data

3. Union records where grouper is missing

4. Note that UNION ALL is used to handle duplicate data.

data have;
infile cards dlm=',' dsd truncover;
input  OBS  ID	 Grouper	 Paid	 VAR2 	 VAR3;
cards;
1,	111,	 ,	100,	150,	200
2,	111,	0,	100,	150,	200
3,	111,	 ,	100,	150,	200
4,	122,	 ,	100,	150,	200
5,	122,	7,	100,	150,	200
6,	123,	1,	100,	150,	200
7,	125,	 ,	100,	150,	200
8,	125,	 ,	100,	150,	200
9,	125,	 ,	100,	150,	200
10,	125,	 ,	100,	150,	200
;;;;
run;

proc sql;
create table list1 as select ID from have where not missing(grouper);
quit;

proc sql;
create table want as
select ID, max(grouper) as grouper, sum(paid)as paid, sum(var2) as var2, sum(var3) as var3
from have
where id in (select id from list1)
group by id
union all
select *
from have (drop=obs) where id not in (select ID from list1);
quit;

proc print data=want;
run;

Re: Sum ifs

novinosrin — Wed, 05 Dec 2018 16:58:40 GMT

Hey @Alexjaco1 Please find the new that handles the updated HAVE. This may look a little more complicated, so I will come back to you with a much simpler one using proc means and append shortly after I finish my coffee.

data have;
input OBS 	ID	Grouper	Paid	VAR2 	VAR3;
drop obs;
cards;
1	111	 .	100	150	200
2	111	0	100	150	200
3	111	 .	100	150	200
4	122	 .	100	150	200
5	122	7	100	150	200
6	123	1	100	150	200
7	125	 .	100	150	200
8	125	 .	100	150	200
9	125	 .	100	150	200
10	125	 .	100	150	200
;

data want;
do until(last.id);
	set have;
	by id;
	if Grouper ne . then do;
	_k=1;
	_g=Grouper;
	end;
end;
do until(last.id);
	set have;
	by id;
	array p(*) paid--var3;
	array s(3);
	if _k then do;
		do _i=1 to dim(p);
		s(_i)=sum(s(_i),p(_i));
		end;
	end;
	else do; 
		do _i=1 to dim(p);
		s(_i)=p(_i);
		end;
		output;
	end;
end;
if _k then do;
	Grouper=_g;
	output;
end;
drop _: paid var:;
run;

Re: Sum ifs

novinosrin — Wed, 05 Dec 2018 17:08:18 GMT

Proc sql


data have;
input OBS 	ID	Grouper	Paid	VAR2 	VAR3;
drop obs;
cards;
1	111	 .	100	150	200
2	111	0	100	150	200
3	111	 .	100	150	200
4	122	 .	100	150	200
5	122	7	100	150	200
6	123	1	100	150	200
7	125	 .	100	150	200
8	125	 .	100	150	200
9	125	 .	100	150	200
10	125	 .	100	150	200
;
proc sql;
create table want as
select id,grouper,sum(paid) as s1,sum(var2) as s2, sum(var3) as s3
from 
(select *, max( Grouper ne .) as k from have group by id having k=1)
group by id
having grouper>.
union all
select id,grouper,paid as s1, var2 as s2, var3 as s3
from (select *, max( Grouper ne .) as k from have group by id having k=0)
order by id;
quit;

Re: Sum ifs

novinosrin — Wed, 05 Dec 2018 17:52:27 GMT

Although long, but very easy, simple and convenient:


data have;
input OBS 	ID	Grouper	Paid	VAR2 	VAR3;
drop obs;
cards;
1	111	 .	100	150	200
2	111	0	100	150	200
3	111	 .	100	150	200
4	122	 .	100	150	200
5	122	7	100	150	200
6	123	1	100	150	200
7	125	 .	100	150	200
8	125	 .	100	150	200
9	125	 .	100	150	200
10	125	 .	100	150	200
;

proc sql;
create table w as
select *
from have 
where id in (select id from have where grouper ne .);
quit;

proc sql;
create table w1 as
select *
from have 
where id in (select id from have group by id having max(grouper) eq .);
quit;

proc means data=w nway noprint;
class id;
var paid--var3;
output out=w2(drop=_:) sum=;
run;

data want;
retain id grouper paid var2 var3;
set w2 w1;
run;

data final_want;
merge want have(keep=id grouper where=(grouper ne .));
by id;
run;