Count only all conditions are

[Faruk]

Hi,
i want to count records only when all conditions of the group are match the accepted value.

Example data :

Car, Type, Engine, Test result
Honda Civic Hybrid Ok
Honda Civic Benz Ok
Honda Civic Diesel Nok
Mercedes Cklass Benz Ok
Mercedes Cklass Hybrid Ok
Mercedes Cklass Diesel Ok
Audi a6 Benz Ok
Audi a6 Hybrid Ok
Tesla x Benz Nok
Tesla x Hybrid Nok
Tesla x Diesel Nok
Opel Insigne Hybrid Unknown
Opel Insigne Diesel Unknown
Opel Insigne Benz Ok
Volvo V60 Benz Ok
Volvo V60 Hybrid Nok
Volvo V60 Diesel Nok
Volvo V60 Electric Unknown
Peugeot 5008 Benz Unknown
Peugeot 5008 Hybrid Unknown
Peugeot 5008 Diesel Unknown

What i want as count is that all cars with same type are counted if all the results of a car type are Ok
and if one of results is Nok or Unknown it counted as Nok
And also counted the cars an type who has Unknown as result.

So the first group is this,
Honda has minimum 1 Nok so its Nok.
Mercedes and Audi have all the records Ok so its Ok
Tesla has all the records Nok
Opel had one result Unknown so is Unknown

Volvo has Unknown and Nok so result is Unknown and nok
And Peugeot has all records Unknown

Car, Type, Engine, Passed, Count
Honda Civic Nok
Mercedes Cklass Ok
Audi a6 Benz Ok
Tesla x Benz Nok
Opel Insigne Hybrid Unknown
Volvo V60 Nok
Volvo V60 Unknown
Peugeot 5008 Benz Unknown

So accepted end solution is:
Cars that are Ok: 2
Cars that are Nok: 3
Cars that also are Unknown: 2

Total car type that are tested: 7

[s_lassen · Accepted solution]

You will have a much better chance of getting an answer if you present your data as a data step, e.g. like this:

data have;
  input Car $ Type $ Engine $ Test_result $;
cards;
Honda Civic Hybrid Ok
Honda Civic Benz Ok
Honda Civic Diesel Nok
Mercedes Cklass Benz Ok
Mercedes Cklass Hybrid Ok
Mercedes Cklass Diesel Ok
Audi a6 Benz Ok
Audi a6 Hybrid Ok
Tesla x Benz Nok
Tesla x Hybrid Nok
Tesla x Diesel Nok
Opel Insigne Hybrid Unknown
Opel Insigne Diesel Unknown
Opel Insigne Benz Ok
Volvo V60 Benz Ok
Volvo V60 Hybrid Nok
Volvo V60 Diesel Nok
Volvo V60 Electric Unknown
Peugeot 5008 Benz Unknown
Peugeot 5008 Hybrid Unknown
Peugeot 5008 Diesel Unknown
;run;

I think you can get the answers you want like this:

data want;
  set have;
  by car type notsorted;
  n_OK+Test_result='Ok';
  n_Nok+Test_result='Nok';
  n_Unknown+Test_result='Unknown';
  if last.type;
  if n_Nok then do;
    Test_result='Nok';
    output;
    if n_Unknown then do;
      Test_result='Unknown';
      output;
      end;
    end;
  else if n_Unknown then do;
    Test_result='Unknown';
    output;
    end;
  else do;
    Test_result='Ok';
    output;
    end;  
  call missing(of n_:);
  drop engine;
run;

Notes:

• Your input data is grouped, but not sorted by CAR and TYPE. So I took the lazy way out and set it BY CAR TYPE NOTSORTED, instead of sorting before the second step. With real life data you may want to sort.
• The SUM statements (e.g. n_OK+Test_result='Ok') automatically retain the result variables.
• So when we reach the final car for a given TYPE, the counts of the test results are in the 3 summary variables
• After outputting the results, the summary variables are set to missing
• The ENGINE variable is not relevant for the output, and varies over CAR and TYPE, so I dropped that for the final output.

But I do not think your specification of results is consistent. On one hand, your output table has two records for the Volvo, but  in the final sum-up you apparently only count the "Nok" record (otherwise, there should be 3 types that are "also Unknown").

[Faruk]

You're right, i will post in the future datalines.

i was adding it from my phone, that's why.

youre solution is not that i wanted but i used it as a first step and use the outecome to count the ok and nok