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.5 2
3 1.8 3
3 1.2 4
3 1.5 5
3 1.6 6
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;
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;
it works. Thank you so much for your prompt help!
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
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
Very Neat!, How much would you or Art charge to train newbie's like me on SAS programming?
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
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
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
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.
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.
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.