topic Re: Help with Summarizing a Group in SAS Programming

Help with Summarizing a Group

anweinbe — Tue, 28 Jul 2020 14:20:33 GMT

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!

Re: Help with Summarizing a Group

PaigeMiller — Tue, 28 Jul 2020 14:31:05 GMT

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

Re: Help with Summarizing a Group

mklangley — Tue, 28 Jul 2020 14:37:15 GMT

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