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.
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;
You have a semi-colon after 'from sss.sales' which shouldn't be there. Rather, the semi-colon should be after group by product.
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.
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;
Thanks a great deal Arthur and stat@sas. Both the replies worked for me. Thank you.
Try this.
proc sql;
create table ttt as
select product,count (product) as total_category
from sss.sales
group by product;
quit;
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.
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.