Contributor
Posts: 44

# sum/average if meet condition

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,

C

PROC Star
Posts: 8,163

## sum/average if meet condition

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;

Contributor
Posts: 44

## Re: sum/average if meet condition

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;

end;

do until (last.id);

set have;

by id;

if first.id then average=total/n;

output;

end;

run;

PROC Star
Posts: 8,163

## Re: sum/average if meet condition

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));

Valued Guide
Posts: 765

## Re: sum/average if meet condition

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        .

Super User
Posts: 13,508

## sum/average if meet condition

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.

Discussion stats
• 5 replies
• 2310 views
• 0 likes
• 4 in conversation