how to delete certain subject

Solved
Occasional Contributor
Posts: 10

how to delete certain subject

if I have a dataset like below, I want to keep subjects that have replicates more than 5, how to do that? I used if replicate <6 then delete, only subject 3, replicate 6 remain.

Thank you!

old

subject     inr         replicate

1              1.5          1

1              1.6          2

1              1.4          3

2              2.1          1

2              1.8          2

3              1.7          1

3              1.5          2

3              1.8          3

3              1.2          4

3              1.5          5

3              1.6          6

new

subject     inr         replicate

3              1.7          1

3              1.5          2

3              1.8          3

3              1.2          4

3              1.5          5

3              1.6          6

Accepted Solutions
Solution
‎12-18-2014 12:59 PM
PROC Star
Posts: 8,169

Re: how to delete certain subject

Easy with proc sql.  e.g.,

data have;

input subject     inr         replicate;

cards;

1              1.5          1

1              1.6          2

1              1.4          3

2              2.1          1

2              1.8          2

3              1.7          1

3              1.5          2

3              1.8          3

3              1.2          4

3              1.5          5

3              1.6          6

;

proc sql;

create table want as

select *

from have

group by subject

having count(*) ge 6

;

quit;

All Replies
Solution
‎12-18-2014 12:59 PM
PROC Star
Posts: 8,169

Re: how to delete certain subject

Easy with proc sql.  e.g.,

data have;

input subject     inr         replicate;

cards;

1              1.5          1

1              1.6          2

1              1.4          3

2              2.1          1

2              1.8          2

3              1.7          1

3              1.5          2

3              1.8          3

3              1.2          4

3              1.5          5

3              1.6          6

;

proc sql;

create table want as

select *

from have

group by subject

having count(*) ge 6

;

quit;

Occasional Contributor
Posts: 10

Re: how to delete certain subject

it works. Thank you so much for your prompt help!

Frequent Contributor
Posts: 115

Re: how to delete certain subject

Nothing beats Art's slick answers, but just for the sake of fun:

data have;

input subject     inr         replicate;

cards;

1              1.5          1

1              1.6          2

1              1.4          3

2              2.1          1

2              1.8          2

3              1.7          1

3              1.5          2

3              1.8          3

3              1.2          4

3              1.5          5

3              1.6          6

;

proc sort data=have out=have1;

by subject descending replicate;

run;

data have2;

set have1;

by subject;

retain replicate2;

if first.subject then replicate2=replicate;

if replicate2<=5 then delete;

drop replicate2;

run;

proc sort data=have2 out=want;

by subject replicate;

run;

Thanks,

Naveen Srinivasan

L&T infotech

Super User
Posts: 10,787

Re: how to delete certain subject

Just to state another powerful skill( DOW + count )( I am about to wright a paper about it ) can replace SQL . and other than SQL , it is more suitable for big table than SQL.

```data have;
input subject     inr         replicate;
cards;
1              1.5          1
1              1.6          2
1              1.4          3
2              2.1          1
2              1.8          2
3              1.7          1
3              1.5          2
3              1.8          3
3              1.2          4
3              1.5          5
3              1.6          6
;
run;

data want(drop=n);
n=0;
do until(last.subject);
set have;
by subject;
n+1;
end;
do until(last.subject);
set have;
by subject;
if n gt 5 then output;
end;
run;

```

Xia Keshan

Frequent Contributor
Posts: 115

Re: how to delete certain subject

Very Neat!, How much would you or Art charge to train newbie's like me on SAS programming?

Super User
Posts: 10,787

Re: how to delete certain subject

I don't know . I think it isn't possible for me to do that, although I would like to , I am in China.

But you could ask Arthur.T , He is available anytime I guess ,and I will inform him to see if he have some interest.

Xia Keshan

PROC Star
Posts: 8,169

Re: how to delete certain subject

Naveen,

What a nice compliment! However, I've decided to spend most of my time from now on supporting the website/database/placement service I've created, namely AnalystFinder.com

There already are a number of good people out there who specialize in SAS training, but virtually no one to help SAS professionals find jobs.

Art

Frequent Contributor
Posts: 115

Re: how to delete certain subject

Dear Sir,

Good Morning from Chennai City, India as it is 2:13AM here. Thank you so much for noticing my mention and for the humble courtesy in replying to my post. I have followed many of your posts thoroughly since 2011 and that's the time I started my career in SAS programming/Analysis. I am fully aware and in awe of your accomplishments all the way through(1970's-till present) in your SAS endeavors and trust me, I am amazed to hear of your popularity across the globe wherever I have travelled, be it Europe, America’s, Singapore and beyond. I have read your interviews online and many more SAS activities of yours. To use a metaphor for comparison, they call you “Roger federer of SAS”, as he is for Tennis sport.

For whatever reason, my interest in SAS programming spurred only in Feb 2014 of this year, perhaps as I turned 31 years of age feeling old (smiles) and regretting to have not accomplished anything in the last 3 years has led my eyes wide open every time to have noticed you et al like PG/Xia Keshan post an amazing immaculate code.

Of course, with daily hard work and perseverance this year, I certainly am better than previous day, however I believe it would be unparalleled to gain superior speed and knack in applying the efficient technique as Xia Keshan did in this thread if you (super experts) were to train folks like me.

Anyways, I can’t thank you enough as I confess to have shamelessly used your code as a verbatim for my work in my past and I bet several others are doing it too. I guess that is really I can think of to write. Thanks for reading and Merry Christmas!!!!.

Cheers,

Naveen

PROC Star
Posts: 8,169

Re: how to delete certain subject

Xia,

That particular paper has already been written a number of times (see, e.g., http://www.devenezia.com/papers/other-authors/sesug-2002/TheMagnificentDO.pdf ).

As shown in that paper, your code can be simplified as:

```data want;
do _n_=1 by 1 until(last.subject);
set have;
by subject;
end;

do until(last.subject);
set have;
by subject;
if _n_ gt 5 then output;
end;
run;
```

While a double DOW definitely has its uses, it definitely doesn't do everything that SQL does, nor does it always perform better. However, yes, in this case the double dow will outperform its SQL equivalent.

🔒 This topic is solved and locked.