Grouping in Proc SQL

[Shivi82]

Hi I am working on a data set where i need to group the data basis product type. However i am unable to do so. I have written 2 codes below.

proc sql;

create table ttt as

select product, count (*) as total category, age, income  

from sss.sales;

group by product

quit;

proc sql;

create table ttt as

select count (product) as new, category, age, income

from sss.sales;

group by product

quit;

However with both the programmes i am unable to find total values for each product type. Product type here is policies such as AIG, Metlife etc. Kindly advice.

[art297]

You have a semi-colon after 'from sss.sales' which shouldn't be there. Rather, the semi-colon should be after group by product.

[Shivi82]

Thanks for the help- that worked. However I have a dataset with 100000+ observations with multiple policy providers hence i need a count of total policies sold by respective companies so that i could know which has the highest market cap.

[art297 · Accepted solution]

You have to be a bit clearer regarding what you want to achieve. There was another problem with your code, but one can't address is without knowing more: is the new count variable supposed to be called total_category, or just total, and category being another variable.

Also, what do you want to do with category, age and income? As is, it will give you the totals, but also provide a record for each record in your data.

Here are two versions that work, but may not provide what you are looking to accomplish:

proc sql;

  create table ttt1 as

    select product, count (*) as total, category, age, income 

      from sss.sales

        group by product

  ;

quit;

proc sql;

  create table ttt2 as

    select product, count (*) as total

      from sss.sales

        group by product

  ;

quit;

[Shivi82]

Thanks a great deal Arthur and stat@sas. Both the replies worked for me. Thank you.

[stat_sas]

Try this.

proc sql;

create table ttt as

select product,count (product) as total_category

from sss.sales

group by product;

quit;