topic Re: Grouping in Proc SQL in SAS Procedures

Grouping in Proc SQL

Shivi82 — Mon, 27 Oct 2014 13:59:07 GMT

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.

Re: Grouping in Proc SQL

art297 — Mon, 27 Oct 2014 14:13:19 GMT

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

Re: Grouping in Proc SQL

Shivi82 — Mon, 27 Oct 2014 14:29:36 GMT

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.

Re: Grouping in Proc SQL

stat_sas — Mon, 27 Oct 2014 15:06:13 GMT

Try this.

proc sql;

create table ttt as

select product,count (product) as total_category

from sss.sales

group by product;

quit;

Re: Grouping in Proc SQL

art297 — Mon, 27 Oct 2014 15:06:24 GMT

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;

Re: Grouping in Proc SQL

Shivi82 — Mon, 27 Oct 2014 15:19:22 GMT

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