sum/average if meet condition

[jkf91]

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

[art297]

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;

[jkf91]

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;

[art297]

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

[MikeZdeb]

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        .

[ballardw]

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.