BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Shivi82
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
art297
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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
Quartz | Level 8

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

stat_sas
Ammonite | Level 13

Try this.

proc sql;

create table ttt as

select product,count (product) as total_category

from sss.sales

group by product;

quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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