topic Re: how to delete certain subject in SAS Procedures

how to delete certain subject

IDM — Thu, 18 Dec 2014 17:32:45 GMT

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

Re: how to delete certain subject

art297 — Thu, 18 Dec 2014 17:59:51 GMT

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;

Re: how to delete certain subject

IDM — Fri, 19 Dec 2014 14:39:14 GMT

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

Re: how to delete certain subject

naveen_srini — Mon, 22 Dec 2014 20:51:00 GMT

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

Re: how to delete certain subject

Ksharp — Tue, 23 Dec 2014 05:36:23 GMT

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

Re: how to delete certain subject

naveen_srini — Tue, 23 Dec 2014 06:47:54 GMT

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

Re: how to delete certain subject

Ksharp — Tue, 23 Dec 2014 07:44:47 GMT

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

Re: how to delete certain subject

art297 — Tue, 23 Dec 2014 15:43:08 GMT

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

Re: how to delete certain subject

art297 — Tue, 23 Dec 2014 16:26:36 GMT

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.

Re: how to delete certain subject

naveen_srini — Tue, 23 Dec 2014 20:47:00 GMT

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