topic Re: deleting observations in SAS Procedures

deleting observations

ab30 — Wed, 08 Jun 2011 18:36:33 GMT

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!

Re: deleting observations

SPR — Wed, 08 Jun 2011 19:26:14 GMT

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

Re: deleting observations

ab30 — Wed, 08 Jun 2011 21:36:29 GMT

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

Re: deleting observations

Ksharp — Thu, 09 Jun 2011 01:47:24 GMT

[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

Re: deleting observations

sbb — Thu, 09 Jun 2011 10:06:39 GMT

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.

Re: deleting observations

Ksharp — Fri, 10 Jun 2011 01:48:57 GMT

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

Ksharp

Re: deleting observations

rtritz — Fri, 17 Jun 2011 00:45:24 GMT

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