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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

7 REPLIES 7
Reeza
Super User

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
Obsidian | Level 7

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
Tourmaline | Level 20

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

Reeza
Super User

@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
Tourmaline | Level 20

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
Super User

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 

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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