Help using Base SAS procedures

deleting observations

Reply
New Contributor
Posts: 2

deleting observations

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!
Super Contributor
Super Contributor
Posts: 365

Re: deleting observations

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
New Contributor
Posts: 2

Re: deleting observations

Thanks! Is there a way to do it without sorting the array? (I need to keep the original order)
Super User
Posts: 9,676

Re: deleting observations

[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
Super Contributor
Super Contributor
Posts: 3,174

Re: deleting observations

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.
Super User
Posts: 9,676

Re: deleting observations

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


Ksharp
Contributor
Posts: 23

Re: deleting observations

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
Ask a Question
Discussion stats
  • 6 replies
  • 144 views
  • 0 likes
  • 5 in conversation