How to write a SQL query to summarize and filter a dataset

[Attyslogin]

data abc;

input name $ sub $ marks;

cards;

a maths 20

a sci 30

b maths 30

b sci 10

c math 30

c sci 25

;

run;

1] here i want names to be appear for single time only
2] need to sum up marks for each name
3] need those name only whose total is greater than fifty.

how to do this using sql ? thanks

[Reeza]

Proc SQL;

create table want as

select name, sum(marks) as total /*2*/

from have

group by name /*1*/

having sum(marks)>50; /*3*/

quit;

[Attyslogin]

what if i want output in this form ?

name  sub     marks total_marks
a         math    10      50
           sci        40        
b        math     30      55
          sci         25

[PeterClemmensen]

And it absolutely HAS to be done in proc sql? 🙂

[Reeza]

---

@Attyslogin wrote:

what if i want output in this form ?

name  sub     marks total_marks
a         math    10      50
           sci        40        
b        math     30      55
          sci         25

---

Then you can use proc report to print it. Or possibly proc tabulate.

[PeterClemmensen]

Something like this?

data abc;
input name $ sub $ marks;
cards;
a maths 20
a sci 30
b maths 30
b sci 10
c math 30
c sci 25
;
run;

proc sql;
   create table want as
   select name
         ,sum(marks) as sumofmarks
   from abc
   group by name
   having sumofmarks > 50
   ;
quit;

[Ksharp · Accepted solution]

Have to use SQL ?

data have;
input name $ sub $ marks;
cards;
a maths 20
a sci 30
b maths 30
b sci 10
b myt 10
c math 30
c sci 25
;
run;
proc sort data=have;by name marks sub;run;

proc sql;
create table key as
select name,min(sub) as sub
from (select *,
(
select count(*) from (select distinct name,marks from have) as b
  where a.name=b.name and b.marks between a.marks and
   (select min(marks) from have where name=a.name)
) as idx
from have as a
where calculated idx=1
)
group by name;


create table want(drop=sum) as
select sum(marks) as sum,
case when not exists(select * from key where name=a.name and sub=a.sub)
 then ' ' else name end as name,
sub,
marks,
case when  not exists(select * from key where name=a.name and sub=a.sub)
 then . else 
 calculated sum end as total

 from have as a
  group by name;
quit;

proc print noobs;run;

 

[Ksharp]

Actually this could be simple as 

data have;
input name $ sub $ marks;
cards;
a maths 20
a sci 30
b maths 30
b sci 10
b myt 10
c math 30
c sci 25
;
run;
proc sql;
select
case when sub=min(sub) then name else ' ' end as new_name,
sub,
marks,
case when sub=min(sub) then sum(marks) else . end as total
 from have
  group by name
   order by name,sub;
quit;