Hello guys, I am new to SAS and having a problem on producing the sub-grouping value in my proc sql after I grouped them once before.
My sample is:
data try;
input ID type $ name $ rate;
datalines;
10 1 orange 0.7
25 1 apple 0.1
25 2 apple 0.1
25 3 apple 0.3
11 2 pear 0.5
11 1 pear 0.3
11 2 pear 0.1
;
run;
What I want is like:
ID Type Name Rate
10 1 orange 0.7
25 1 apple 0.5
2 apple
3 apple
11 1 pear 0.9
2 pear
My code is:
proc sql;
create table try2 as select distinct *, sum(rate) as rate2 from try
group by ID, name
order by ID, name;
quit;
My output can not display only all the data. How can I just get the subgroup total and only displaying the ID as once. The summed rate is not necessarily matched with the type. The idea is showing the ID's subgroup total rate.
Thank you very much for all help.
Hi @Woodyfc
Here is an approach to do this:
proc sql;
create table try2 as
select distinct ID, type, name, sum(rate) as rate2 from try
group by ID, name
order by ID, name;
quit;
proc report data=try2;
columns ID type name rate2;
define ID / group;
define rate2 / group;
run;
Hi @Woodyfc
Here is an approach to do this:
proc sql;
create table try2 as
select distinct ID, type, name, sum(rate) as rate2 from try
group by ID, name
order by ID, name;
quit;
proc report data=try2;
columns ID type name rate2;
define ID / group;
define rate2 / group;
run;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.