Help with Summarizing a Group

[anweinbe]

Good morning.

I have the following data:

DATE        TIC         STYPE          Name

1/1/20        MSFT         Product               Electronics

1/1/20        MSFT         Product               Computers

1/1/19        MSFT         Product               Electronics

1/1/19        MSFT         Product               Computers

1/1/20        GOOG        Foreign               Web Services

1/1/20        GOOG        Foreign               Hosting

1/1/20        GOOG        Product               Computers

I am looking to create a summarized table that just tell me for each data and ticker how many unique segments there are. The oupout should look like this:

1/1/20 MSFT   Product   2

1/1/19 MSFT   Product   2

1/1/20 GOOG   Foreign 2

1/1/20 GOOG   Product 1

I tried this but I am getting an error. I know I took a sum statement out but in my other code I was trying to add numbers up so that wouldn't apply here. I believe I am close!

proc sql;
create table temp.SEGMENTS_2 as
select TIC,
from temp.SEGMENTS_1
group by DATE, STYPE;
quit;

Thank you!

[PaigeMiller]

proc freq data=have;
     tables date*tic*product/list;
run;

[mklangley · Accepted solution]

Try this:

proc sql;
    create table temp.SEGMENTS_2 as
    select DATE, TIC, STYPE, count(Name) as count
    from temp.SEGMENTS_1
    group by DATE, TIC, STYPE;
quit;

When you use a summary statement (like count() or sum()), any columns that you include in the SELECT statement should also be included in the GROUP BY statement. (In this case, since you want DATE, TIC, and STYPE in your ouput, they should also be in your GROUP BY list.)