How to count the total values per group

[PaigeMiller]

There were two questions:

1. "Please explain how you would know the entire set of all possible places where a zero would appear." 
2. "Your original output has SS-X and phase 2 with Total = 0, but when you go to SS-Y there is no phase 2 with Total = 0 in the output data set. Why is that?" Specifically, why is there not an output row that says 
   

   SS-Y 202102 2 0

[Andalusia]

Aha. I understand your questions, Its a quiet complex situation. Hope I can clarify it by this. PS. Thank you for your help 🙂

Answering your 1st question. There are 3 different phases which can appear in the column PHASE (1,2 and 3). There is a possibility that a certain type can contain only phase 1 and 3 (count phase 2 is automatically zero then regardless of the quarter) there is also a possibility that a type can contain phase 1,2 and 3. But there is also a possibility that a type can contain only phase 1 (which automatically means that count type 2 and 3 are zeros regardless of the quarter).

Answering your 2nd question. The SS-Y types only contain a phase 1 and 3. So phase 2 is automatically 0. The SS-X types contains phase 1 and 2 but quarter 202001 does not contain phase 2, that's why that one contains a zero.

Conclusion: phase 1,2 and 3 always need to be counted even if a type does not contain a certain phase.

[PaigeMiller]

Before I put forward a solution, allow me to ask about the real data that you will be applying this to, not the 8 row data set which is clearly an example.

How many values of TYPE (after removing the last two characters, as we are doing) and how many values of quarter? Apparently, there can be at most 3 phases.

[PaigeMiller]

Also, do all values of QUARTER appear for all values of TYPE (after removing the last two characters)?

[Andalusia]

Oke about the real dataset. TYPE can have 4 different values. QUARTER can have 9 different values (4 per year (2019, 2020) and 1 for this year(2021).

No, not all values of QUARTER appear for all values of TYPE. It is possible that a QUARTER occurs in TYPE X but not in TYPE Z. That means that the concerned TYPE was not measured during that quarter.

[PaigeMiller]

data intermediate;
    set have;
    type=substr(type,1,4);
run;

proc freq data=intermediate;
    tables type*quarter*phase/list noprint out=outputdatasetname(drop=percent rename=(count=total)) sparse;
run;