How to count the total values per group

[Andalusia]

I have a dataset which looks like below:

data have;
  infile datalines truncover;
  input type $ quarter phase;
  datalines;
SS-X01 202001 1
SS-X01 202002 1
SS-X02 202001 1
SS-X02 202002 2
SS-Y02 202102 1
SS-Y02 202103 3
SS-Y01 202102 1
SS-Y01 202103 3
;

I'm trying to do the following things:

1. I'm trying to calculate the total count of the phases for every quarter.
2. I'm trying to add (or change) the type column which selects only the values without everything that comes after the XX-LETTER.

I want my dataset to look like this:

type quarter phase total

SS-X 202001 1 2
SS-X 202002 1 1
SS-X 202001 2 0
SS-X 202002 2 1
SS-Y 202102 1 2
SS-Y 202103 1 0
SS-Y 202102 3 0
SS-Y 202103 3 2

[PeterClemmensen]

Do you need to do this by type? I don't completely follow your logic, please be more specific.

[Andalusia]

I want to do it by quarter and type. If you look to my desired output you can see that for Type X quarter 202001 I have two 1's. And for Type Y quarter I have 202103 I have two 3's

[PaigeMiller]

I don't see how you get this result in the output

SS-Y 202102  1     2

as I think the result ought to be a 1 in the final column for this row. Could you please explain?

[Andalusia]

That means for type Y quarter 202102 I have two 1's. Get it? Im counting how much PHASE occurs.

[PaigeMiller]

data intermediate;
    set have;
    type=substr(type,1,4);
run;
proc freq data=intermediate;
    tables type*quarter*phase/list nopercent nocum;
run;

[Andalusia]

What does the 1,4 in your code stand for? What for example if that was 1,10?

[PaigeMiller]

substr(type,1,4)

indicates we want to take part of the value of variable TYPE ... which parts? we start at the first character and continue for until a total of four characters has been selected

So if the value of TYPE is SS-X01, then we take the first four characters, resulting in SS-X

If it said substr(type,2,10), we would select the characters starting with the second character and continue until 10 characters had been selected.

[Andalusia]

Oke but where is the Total column? I only see the new Type column?

[PaigeMiller]

TOTAL is found in the output from PROC FREQ. I believe the default column name used in PROC FREQ is Frequency.

[Andalusia]

@PaigeMiller Oke but this does not look the same as my desired outcome. Its just a frequency ...I also want it to be a dataset and not a report .

[PaigeMiller]

This is not the output I get. Please show the code you are using.

[Andalusia]

@PaigeMiller This is my code:

data have;
  infile datalines truncover;
  input type $ quarter phase;
  datalines;
SS-X01 202001 1
SS-X01 202002 1
SS-X02 202001 1
SS-X02 202002 2
SS-Y02 202102 1
SS-Y02 202103 3
SS-Y01 202102 1
SS-Y01 202103 3
;

data intermediate;
    set have;
    type=substr(type,1,4);
run;
proc freq data=intermediate;
    tables type*quarter*phase/list nopercent nocum;
run;

This is intermediate:

This is FREQ Procedure:

Two things:
* I want the zero frequencies also.
* I want it to be a dataset not a report because I need to do some other things with it after this.

[PaigeMiller]

To create a data set, use

tables type*quarter*phase/list noprint out=outputdatasetname;

Please explain how you would know the entire set of all possible places where a zero would appear. For example, 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?

[Andalusia]

@PaigeMiller Oké. Can you explain your question regarding the zeros? I didn't understand it