BookmarkSubscribeRSS Feed
ab30
Calcite | Level 5
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!
6 REPLIES 6
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Calcite | Level 5
Thanks! Is there a way to do it without sorting the array? (I need to keep the original order)
Ksharp
Super User
[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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Super User
Yes.That is a good way.
But If you have a large dataset?which will waste lots of time.


Ksharp
rtritz
Calcite | Level 5
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 799 views
  • 0 likes
  • 5 in conversation