Counting in SAS with multiple conditions by group

[Chira85]

Hello group!

I have the following dataset with the following variables: Animal date_of_service type_of_service and type_of_diagnostic.

I want to do by animal:
count since the first type_of_diagnostic="Negative" (here the type of service can be natural or insemination), how many observations fulfilling these conditions: type_of_service="Natural" and type_of_diagnostic="Negative" until the first type_of_diagnostic being "positive".

Thank you in advance for any help!

Animal date_of_service type_of_service type_of_diagnostic
1 07/06/2021 Natural Negative
1 01/10/2021 Insemination Negative
1 12/01/2022 Natural Negative
1 03/02/2022 Insemination Negative
1 01/03/2022 Natural Positive

Results wanted:
Animal count
1 1

[PaigeMiller]

Please, from now on present data as WORKING data step code, like this:

data have;
input Animal date_of_service :ddmmyy10. type_of_service :$12. type_of_diagnostic $;
cards;
1 07/06/2021 Natural Negative
1 01/10/2021 Insemination Negative
1 12/01/2022 Natural Negative
1 03/02/2022 Insemination Negative
1 01/03/2022 Natural Positive
;

Your problem statement isn't particularly clear. Do you want a count of the number of animals that meet this condition, as your output appears to be showing? Or do you want, as you said, "how many observations fulfilling these conditions". Please clarify this.

Furthermore, to improve the presentation of the problem and help us find a correct solution, please keep in mind these important items

• When you show the desired output, you need to show the desired output for this input data. The answer, if I am understanding you properly, cannot be 11 for this input data.
• If DATE_OF_SERVICE is not needed, don't include it.
• If the real data can have more than 1 animal, please provide sample data and output for the case where there are at least two animals.

[Chira85]

I am sorry for the errors. The data set has more than one animal and I want to count for each animal, how many observations fulfilling these conditions. Below is the corrected code.

Thank you!

data have;
input Animal type_of_service $14. type_of_diagnostic $12.;
cards;
1 Natural Negative
1 Insemination Negative
1 Natural Negative
1 Insemination Negative
1 Natural Positive
2 Insemination Negative
2 Natural Negative       
2 Natural Negative
2 Insemination Positive
;
run;

Results wanted:
Animal count
1       1
2       2

[PaigeMiller]

Your code doesn't create the data set properly.

[Chira85]

data have;
input Animal type_of_service :$12. type_of_diagnostic $12.;
cards;
1 Natural Negative
1 Insemination Negative
1 Natural Negative
1 Insemination Negative
1 Natural Positive
2 Insemination Negative
2 Natural Negative       
2 Natural Negative
2 Insemination Positive
;

[PaigeMiller]

data want;
    set have;
    by animal;
    if first.animal then do;
        count=0;
        begin_count=0;
        stop_count=0;
    end;
    if type_of_diagnostic='Negative' then begin_count=1;
    if begin_count and not stop_count and type_of_service='Natural' and type_of_diagnostic='Negative' then count+1;
    if type_of_diagnostic='Positive' then stop_count=1;
    if last.animal then output;
    keep animal count;
run;     

[Chira85]

I have tried it and it works. Thank you very much!