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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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;

 Capture d’écran 2020-01-06 à 18.49.23.png

 

View solution in original post

4 REPLIES 4
ed_sas_member
Meteorite | Level 14

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;

 Capture d’écran 2020-01-06 à 18.49.23.png

 

Reeza
Super User
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
Fluorite | Level 6
Thanks!! I was too focused on proc sql, forgetting the alternative procedure haha.
Woodyfc
Fluorite | Level 6
@Reeza Thanks for your reminders!!:D

SAS Innovate 2025: Register Now

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!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1855 views
  • 4 likes
  • 3 in conversation