topic How to count the total values per group in SAS Enterprise Guide

How to count the total values per group

Andalusia — Tue, 20 Apr 2021 11:56:12 GMT

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:

I'm trying to calculate the total count of the phases for every quarter.
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

Re: How to count the total values per group

PeterClemmensen — Tue, 20 Apr 2021 11:46:56 GMT

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

Re: How to count the total values per group

PaigeMiller — Tue, 20 Apr 2021 11:49:09 GMT

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?

Re: How to count the total values per group

Andalusia — Tue, 20 Apr 2021 11:58:06 GMT

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

Re: How to count the total values per group

Andalusia — Tue, 20 Apr 2021 12:01:03 GMT

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

Re: How to count the total values per group

PaigeMiller — Tue, 20 Apr 2021 12:06:16 GMT

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

Re: How to count the total values per group

Andalusia — Tue, 20 Apr 2021 12:11:07 GMT

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

Re: How to count the total values per group

PaigeMiller — Tue, 20 Apr 2021 12:25:49 GMT

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.

Re: How to count the total values per group

Andalusia — Tue, 20 Apr 2021 12:34:44 GMT

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

Re: How to count the total values per group

PaigeMiller — Tue, 20 Apr 2021 12:38:44 GMT

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

Re: How to count the total values per group

Andalusia — Tue, 20 Apr 2021 12:40:57 GMT

@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 .

Re: How to count the total values per group

PaigeMiller — Tue, 20 Apr 2021 12:44:04 GMT

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

Re: How to count the total values per group

Andalusia — Tue, 20 Apr 2021 13:03:30 GMT

@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.

Re: How to count the total values per group

PaigeMiller — Tue, 20 Apr 2021 13:20:36 GMT

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?

Re: How to count the total values per group

Andalusia — Tue, 20 Apr 2021 13:39:56 GMT

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

Re: How to count the total values per group

PaigeMiller — Tue, 20 Apr 2021 13:45:27 GMT

There were two questions:

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

Re: How to count the total values per group

Andalusia — Tue, 20 Apr 2021 14:08:09 GMT

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.

Re: How to count the total values per group

PaigeMiller — Tue, 20 Apr 2021 14:28:49 GMT

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.

Re: How to count the total values per group

PaigeMiller — Tue, 20 Apr 2021 14:30:28 GMT

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

Re: How to count the total values per group

Andalusia — Tue, 20 Apr 2021 14:43:33 GMT

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.