BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
IDM
Calcite | Level 5 IDM
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

9 REPLIES 9
art297
Opal | Level 21

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;

IDM
Calcite | Level 5 IDM
Calcite | Level 5

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

naveen_srini
Quartz | Level 8

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

Ksharp
Super User

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

naveen_srini
Quartz | Level 8

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

Ksharp
Super User

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

art297
Opal | Level 21

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

naveen_srini
Quartz | Level 8

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. Smiley Happy

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

art297
Opal | Level 21

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.

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
  • 9 replies
  • 2061 views
  • 3 likes
  • 4 in conversation