topic Re: group sum with conditions inside a condition in SAS Programming

group sum with conditions inside a condition

CHL0320 — Mon, 05 Dec 2022 15:04:25 GMT

Hi Community,

I have a long dataset while there are multiple records per id. I want to create a dataset and it is one record per id under the conditions as:

if condition a=1, then under the condition b=0, get a total sum of d. When the last id, use that total sum to be divided by e to get f and output.

I have test the codes using a single id is fine (where statement),however, if I use it into more ids, the answer is wrong. There are somethings I miss in the codes. Or please suggest a better way to do it. Your suggestion is appreciated.

data want;
set have;*sorted;
by id;
retain total;

/*where id =aa;*/
if a=1 then
do;
if b = 0 then
do;
if first.id then
total=d;
else total=sum(total,d);
end;

if last.id then
do;
f=total/e;
output;
end;
end;
run;

Re: group sum with conditions inside a condition

PaigeMiller — Mon, 05 Dec 2022 15:53:33 GMT

UNTESTED CODE since you did not provide data

/* UNTESTED CODE */
data want;
    set have;
    by id;
    if first.id then total=0;
    if a=1 and b=0 then total+d;
    if last.id then do;
        f=total/e;
        output;
    end;
run;

Re: group sum with conditions inside a condition

CHL0320 — Mon, 05 Dec 2022 16:21:12 GMT

Here is the sample dataset:

have

ID	a	b	d	e
aa	1	0	10	3
aa	2	0	9	3
aa	1	1	7	3
aa	1	0	4	3
aa	2	1	8	3
aa	1	0	3	3
aa	2	1	8	3
bb	1	0	1	2
bb	1	0	4	2
bb	2	1	3	2
bb	1	0	6	2
bb	1	0	6	2
bb	1	1	9	2
bb	2	0	4	2

Want

ID	a	b	d	e	f
aa	1	0	10	3	5.666667
bb	1	0	12	2	15

Re: group sum with conditions inside a condition

PaigeMiller — Mon, 05 Dec 2022 16:25:14 GMT

Since you have the data and you have the code, I request that you test it.

Re: group sum with conditions inside a condition

CHL0320 — Mon, 05 Dec 2022 16:26:20 GMT

I did. Thank you and it works.

Re: group sum with conditions inside a condition

CHL0320 — Tue, 27 Dec 2022 21:23:32 GMT

I have a different condition but the first.id if it is in the condition (a=1 and b=1) cannot be set to 0, and it shoule be the value of c. How to deal with this situtation? Thank you for your helps.

Here is the sample data.

data have;
infile datalines dlm="09"x dsd truncover;
input id $ a b c d;
datalines;
aa	1	1	10	3
aa	2	0	9	3
aa	1	1	7	3
aa	1	0	4	3
aa	2	1	8	3
aa	1	0	3	3
aa	2	1	8	3
bb	1	0	6	2
bb	1	1	3	2
bb	2	1	7	2
bb	1	0	6	2
bb	1	0	6	2
bb	1	1	9	2
bb	2	0	4	2
bb	1	1	8	2
bb	1	0	4	2
bb	1	1	3	2
cc	1	1	4	2
cc	1	0	6	2
cc	1	1	9	2
cc	2	0	4	2
;
run;

The data I want is as

id	total	e
aa	17	5.666667
bb	23	11.5
cc	13	6.5

Re: group sum with conditions inside a condition

PaigeMiller — Tue, 27 Dec 2022 21:43:56 GMT

Please provide a more clear explanation. I don't understand what you are asking.

Also, what is column E?

Re: group sum with conditions inside a condition

CHL0320 — Wed, 28 Dec 2022 07:45:03 GMT

The condition of the sum is : within ID, sum(c) to get total for all records in which a=1 and b=1 and then divide total by d to get e.

In the cases, id="aa" or "cc", their first ids fit the condition of a=1 and b=1, while the first id of case "bb" did not fit the condition of a=1 and b=1.

Re: group sum with conditions inside a condition

CHL0320 — Wed, 28 Dec 2022 14:46:29 GMT

I am able to get the result using codes like below.

data want (keep=id total e);
	set have;
	by id;
	where a=1;

	if b^=1 then
		sum=0;
	else if b=1 then
		sum=c;

	if first.id then
		total=0;
	total + sum;

	if last.id then
		do;
			e=total/d;
			output;
		end;
run;

ID	a	b	d	e
aa	1	0	10	3
aa	2	0	9	3
aa	1	1	7	3
aa	1	0	4	3
aa	2	1	8	3
aa	1	0	3	3
aa	2	1	8	3
bb	1	0	1	2
bb	1	0	4	2
bb	2	1	3	2
bb	1	0	6	2
bb	1	0	6	2
bb	1	1	9	2
bb	2	0	4	2

ID	a	b	d	e
aa	1	0	10	3
aa	2	0	9	3
aa	1	1	7	3
aa	1	0	4	3
aa	2	1	8	3
aa	1	0	3	3
aa	2	1	8	3
bb	1	0	1	2
bb	1	0	4	2
bb	2	1	3	2
bb	1	0	6	2
bb	1	0	6	2
bb	1	1	9	2
bb	2	0	4	2

ID	a	b	d	e
aa	1	0	10	3
aa	2	0	9	3
aa	1	1	7	3
aa	1	0	4	3
aa	2	1	8	3
aa	1	0	3	3
aa	2	1	8	3
bb	1	0	1	2
bb	1	0	4	2
bb	2	1	3	2
bb	1	0	6	2
bb	1	0	6	2
bb	1	1	9	2
bb	2	0	4	2