topic Re: How do I find the sum of subgroup in proc SQL in my grouped data? in SAS Procedures

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

Woodyfc — Mon, 06 Jan 2020 17:16:52 GMT

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.

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

ed_sas_member — Mon, 06 Jan 2020 17:49:56 GMT

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;

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

Reeza — Mon, 06 Jan 2020 18:08:23 GMT

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.

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

Woodyfc — Tue, 07 Jan 2020 00:21:35 GMT

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

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

Woodyfc — Tue, 07 Jan 2020 00:22:48 GMT

@Reeza Thanks for your reminders!!:D