topic Re: How to write a SQL query to summarize and filter a dataset in SAS Programming

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

Attyslogin — Sat, 05 Nov 2016 17:16:46 GMT

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

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

Reeza — Sat, 05 Nov 2016 17:19:29 GMT

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;

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

PeterClemmensen — Sat, 05 Nov 2016 17:25:35 GMT

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;

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

Attyslogin — Sat, 05 Nov 2016 17:29:31 GMT

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

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

PeterClemmensen — Sat, 05 Nov 2016 17:33:58 GMT

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

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

Reeza — Sat, 05 Nov 2016 21:26:57 GMT

@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.

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

Ksharp — Sun, 06 Nov 2016 05:58:37 GMT

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;

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

Ksharp — Sun, 06 Nov 2016 11:02:46 GMT

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;