How do I find the sum of subgroup in proc SQL in my grouped data?

[Woodyfc]

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. 

[ed_sas_member · Accepted solution]

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;

 

[Reeza]

When you select distinct *, you're saying take the unique values for each line, which INCLUDES the rate. You don't want the rate for each value though, you want that summarized so you cannot use the * notation in your query.

Most other SQL types (Oracle, DB2) would cause this to result in an error but in SAS this is allowed and can be quite useful. You should be seeing a note about re-merging in the log though, which should raise a flag for you.

[Woodyfc]

Thanks!! I was too focused on proc sql, forgetting the alternative procedure haha.

[Woodyfc]

@Reeza Thanks for your reminders!!:D