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

## by group with all missing value

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

## Re: by group with all missing value

``````
proc sql;
create table want as
select *
from have
group by Grade, ST_ID
having n(Course)=0;
quit;``````
3 REPLIES 3
Tourmaline | Level 20

## Re: by group with all missing value

``````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;
``````
Lapis Lazuli | Level 10

## Re: by group with all missing value

Thanks Novinorsin
Super User

## Re: by group with all missing value

``````
proc sql;
create table want as
select *
from have
group by Grade, ST_ID
having n(Course)=0;
quit;``````
Discussion stats
• 3 replies
• 1635 views
• 2 likes
• 3 in conversation