topic by group with all missing value in SAS Data Management

by group with all missing value

mlogan — Fri, 21 Sep 2018 17:35:46 GMT

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

Re: by group with all missing value

novinosrin — Fri, 21 Sep 2018 18:02:24 GMT

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;

Re: by group with all missing value

Ksharp — Sat, 22 Sep 2018 10:48:37 GMT


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

Re: by group with all missing value

mlogan — Mon, 24 Sep 2018 13:39:02 GMT

Thanks Novinorsin