topic sum/average if meet condition in SAS Procedures

sum/average if meet condition

jkf91 — Fri, 28 Oct 2011 17:00:19 GMT

Hi all,

I have the following code (continuation from an earlier post):

data want;

do until (last.id);

set have;

by id;

if first.id then do;

n=0;

total=0;

end;

if month le 6 then total+packs and n+1;

end;

do until (last.id);

set all_no09.Hh_all12w_hhsize_short;

by id;

if first.id then

average=total/n;

output;

end;

run;

For some reason, it's not using the if condition (month le 6)...

Does anyone know why?

Thanks,

sum/average if meet condition

art297 — Fri, 28 Oct 2011 17:03:03 GMT

Its not working because the statement isn't written correctly. I think you wanted something like:

if month le 6 then do;

total+packs;

n+1;

end;

Re: sum/average if meet condition

jkf91 — Fri, 28 Oct 2011 17:54:42 GMT

still doesn't work. still adds up everything disregarding the "month le 6" condition.

data want;

do until (last.id);

set have;

by id;

if first.id then do;

n=0;

total=0;

end;

if month<6 then do;

total +packs;

n+1;

end;

do until (last.id);

set have;

by id;

if first.id then average=total/n;

output;

end;

run;

Re: sum/average if meet condition

art297 — Fri, 28 Oct 2011 18:01:01 GMT

Depends upon what you are trying to do. The statements, as I wrote them, will exclude records from the two sums, but not exclude them from the output. If you want to exclude all data that has a month that isn't less than 6, then I would simply include a where option in your two set statements.

I can't test the specific syntax at the moment, but you could probably use something like:

set have (where=(month>5));

Re: sum/average if meet condition

MikeZdeb — Fri, 28 Oct 2011 20:18:51 GMT

Hi ... here are a couple suggestions, seems to work OK (is that output what you actually want ?).

data have;

input id month packs @@;

datalines;

1 1 10 1 2 20 1 8 99 1 5 30 1 1 30

2 1 100 2 3 200 2 7 999 2 3 300 2 8 888

3 10 999 3 11 999

;

run;

data want;

total = 0; n = 0;

do until (last.id);

set have;

by id;

total + packs * (month lt 6);

n + (month lt 6);

end;

if n then average = total / n;

do until (last.id);

set have;

by id;

output;

end;

run;

id month packs total n average

1 1 10 90 4 22.5

1 2 20 90 4 22.5

1 8 99 90 4 22.5

1 5 30 90 4 22.5

1 1 30 90 4 22.5

2 1 100 600 3 200.0

2 3 200 600 3 200.0

2 7 999 600 3 200.0

2 3 300 600 3 200.0

2 8 888 600 3 200.0

3 10 999 0 0 .

3 11 999 0 0 .

sum/average if meet condition

ballardw — Fri, 28 Oct 2011 20:26:40 GMT

I think you may not be getting some of your output because of the two DO UNTIL(LAST.ID); loops. With an explicit OUTPUT in the second one then that is the ONLY place SAS is outputting the data. You may want another OUTPUT in the first loop as well.

I would guess that you might be looking for a:

IF LAST.ID THEN OUTPUT;

And you might want a RETAIN TOTAL N .; at the begining of your data step.