deleting observations

[ab30]

Hi, I am new to SAS and have a basic question. Lets say I have the following table:

ID Grade
1 3.2
1 3.7
1 .
1 3.5
2 2.7
2 4

and I need to remove all IDs that have at least one grade missing meaning the final output should be:
ID Grade
2 2.7
2 4

How should I approach this?

Thanks!

[SPR]

Hello AB30,

This is a solution:
[pre]
data i;
input ID Grade;
datalines;
1 3.2
1 3.7
1 .
1 3.5
2 2.7
2 4
run;
proc sort data=i;
by ID Grade;
run;
data r;
retain keep;
set i;
if FIRST.ID then keep=1;
if FIRST.ID and Grade = . then keep=0;
if keep then output;
by ID;
drop keep;
run;
[/pre]
Sincerely,
SPR

[ab30]

Thanks! Is there a way to do it without sorting the array? (I need to keep the original order)

[Ksharp]

[pre]



data i;
input ID Grade;
datalines;
1 3.2
1 3.7
1 .
1 3.5
2 2.7
2 4
run;

data want(drop=rc);
declare hash ha (hashexp:10);
ha.definekey('id');
ha.definedone();

do until(last);
set i end=last;
if missing(grade) then rc=ha.add();
end;
do until(_last);
set i end=_last;
rc=ha.check();
if rc ne 0 then output;
end;
stop;
run;
[/pre]


Ksharp

[sbb]

To get the file back into original order, you can capture _N_ as your variable (choose a name like OBSNUM) and then the last task in your SAS program would be to re-sort the file back into its original sequence using OBSNUM.

Scott Barry
SBBWorks, Inc.

[Ksharp]

Yes.That is a good way.
But If you have a large dataset?which will waste lots of time.


Ksharp

[rtritz]

Just to add some variety, you can modify your input data as suggested by sbb and then use a proc sql step to generate your table.

data grades;
input id grade;
obs = _n_;
datalines;
1 3.2
1 3.7
1 .
1 3.5
2 2.7
2 4
;
run;

proc sql;
create table grades2 as
select id, grade
from grades
group by id
having sum(grade=.)=0
order by obs;
quit;

Rich