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
proc sql;
create table want as
select *
from have
group by Grade, ST_ID
having n(Course)=0;
quit;
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;
proc sql;
create table want as
select *
from have
group by Grade, ST_ID
having n(Course)=0;
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!
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.