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
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;
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;
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
And it absolutely HAS to be done in proc sql? 🙂
@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.
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.