DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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

[ Edited ]

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


Accepted Solutions
Solution
‎11-07-2016 01:20 PM
Super User
Posts: 9,681

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

[ Edited ]

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;

x.png 

View solution in original post


All Replies
Super User
Posts: 17,829

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

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;

Contributor
Posts: 29

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

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

PROC Star
Posts: 551

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

And it absolutely HAS to be done in proc sql? Smiley Happy

Super User
Posts: 17,829

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


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.

PROC Star
Posts: 551

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

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;
Solution
‎11-07-2016 01:20 PM
Super User
Posts: 9,681

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

[ Edited ]

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;

x.png 

Super User
Posts: 9,681

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

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 287 views
  • 0 likes
  • 4 in conversation