Help using Base SAS procedures

Grouping in Proc SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

Grouping in Proc SQL

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.


Accepted Solutions
Solution
‎10-27-2014 11:06 AM
PROC Star
Posts: 7,360

Re: Grouping in Proc SQL

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;

View solution in original post


All Replies
PROC Star
Posts: 7,360

Re: Grouping in Proc SQL

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

Frequent Contributor
Posts: 92

Re: Grouping in Proc SQL

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.

Solution
‎10-27-2014 11:06 AM
PROC Star
Posts: 7,360

Re: Grouping in Proc SQL

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;

Frequent Contributor
Posts: 92

Re: Grouping in Proc SQL

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

Trusted Advisor
Posts: 1,204

Re: Grouping in Proc SQL

Try this.

proc sql;

create table ttt as

select product,count (product) as total_category

from sss.sales

group by product;

quit;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 246 views
  • 3 likes
  • 3 in conversation