BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlogan
Lapis Lazuli | Level 10

Hi All,

I want to find all the by group observations where one of the specific variable is missing on all.

Can anyone help please. Thanks.

 

 

data have;
input Grade $1-7 ST_ID $9-11 Course $13-16 Score$17-18 date;
informat date ddmmyy8.;
format date date9.;
cards; 
Grade10 101 Eng	20 01/01/82
Grade11 105 Bio	25 25/11/99
Grade11 105 Math18 23/09/00
Grade11 105 Eng 15 11/12/01
Grade11 105 Math15 14/12/05
Grade11 102 Eng 15 13/07/02
Grade11 102 Bio 20 22/11/07
Grade11 102 Phy 16 19/04/88
Grade11 102 Math21 27/08/99
Grade11 104 Arts13 18/06/00
Grade11 104 Che 12 14/07/09
Grade11 104 Eng 18 09/03/08
Grade11 104 Bio 21 25/11/13
Grade12 103 Bio 10 13/12/99
Grade12 103 Math20 25/05/01
Grade12 103 Eng 16 04/04/06
Grade12 103     15 09/11/02
Grade12 103 Phy 17 16/03/15
Grade12 106     17 14/03/16
Grade12 106     19 11/05/01
Grade12 106     10 13/12/99
;
RUN;

 

 

Expected Out put Table:

Grade   St_ID Course  Score  Date

Grade12 106                17        14/03/16
Grade12 106                19        11/05/01
Grade12 106                10        13/12/99

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

proc sql;
create table want as
select *
from have
group by Grade, ST_ID 
having n(Course)=0;
quit;

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20
data have;
input Grade $1-7 ST_ID $9-11 Course $13-16 Score$17-18 date;
informat date ddmmyy8.;
format date date9.;
cards; 
Grade10 101 Eng	20 01/01/82
Grade11 105 Bio	25 25/11/99
Grade11 105 Math18 23/09/00
Grade11 105 Eng 15 11/12/01
Grade11 105 Math15 14/12/05
Grade11 102 Eng 15 13/07/02
Grade11 102 Bio 20 22/11/07
Grade11 102 Phy 16 19/04/88
Grade11 102 Math21 27/08/99
Grade11 104 Arts13 18/06/00
Grade11 104 Che 12 14/07/09
Grade11 104 Eng 18 09/03/08
Grade11 104 Bio 21 25/11/13
Grade12 103 Bio 10 13/12/99
Grade12 103 Math20 25/05/01
Grade12 103 Eng 16 04/04/06
Grade12 103     15 09/11/02
Grade12 103 Phy 17 16/03/15
Grade12 106     17 14/03/16
Grade12 106     19 11/05/01
Grade12 106     10 13/12/99
;
RUN;

proc sql;
create table want as
select *
from have
group by Grade, ST_ID 
having sum(Course=' ')=count(*);
quit;
mlogan
Lapis Lazuli | Level 10
Thanks Novinorsin
Ksharp
Super User

proc sql;
create table want as
select *
from have
group by Grade, ST_ID 
having n(Course)=0;
quit;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1641 views
  • 2 likes
  • 3 in conversation