BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Barkamih
Pyrite | Level 9
herd                     cow_id
10                           101
10                           101
10                           101
10                           101
10                           101
10                           101 
10                           101
10                           102
10                           102
10                           102
10                           102
10                           103
10                           104
10                           104
10                           104
10                           105
11                           106
11                           106
11                           106
11                           106
11                           106
11                           106
11                           106
11                           106
11                           106
11                           107
11                           107
11                           108
11                           108
11                           108
11                           108
11                           108
11                           108
11                           108 

I just wanted to count the number of cow_id for each herd that I have in my dataset. 

Data named: Fatd  and    I have variable herd and coe_id,  I want to delete all herds that have less than 5 cow_ids.

 

If you look at this data, Herd 10 has 5 cows ID (101, 102, 103, 104, 105). the repetition 7 times of  cow_id 101 mean this cow has 7 times of measurements, so in my question, I don't care about the repetition, I'm just looking for cow_id.   And herd 11 has 3 cows ID (106, 107, 108), so in this case, I just want to keep herd 10 and delete herd 11, it does not matter how much each cow had been repeated. 

 

I just care about the number of cows in each herd not about the number of measurements.  (Repeated cow_id is a different time of measurements). 

 

I hope you get my idea this time 

 

 

 

Regards 

 

Ibrahim 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

Something like this?

 

data have;
input herd cow_id;
datalines;
10 101
10 101
10 101
10 101
10 101
10 101
10 101
10 102
10 102
10 102
10 102
10 103
10 104
10 104
10 104
10 105
11 106
11 106
11 106
11 106
11 106
11 106
11 106
11 106
11 106
11 107
11 107
11 108
11 108
11 108
11 108
11 108
11 108
11 108
;

proc sql;
   create table want as
   select * from have
   where herd in 
   (select distinct herd from have group by herd having count(distinct cow_id) >= 5);
quit;
                  

View solution in original post

14 REPLIES 14
PeterClemmensen
Tourmaline | Level 20

Something like this?

 

data have;
input herd cow_id;
datalines;
10 101
10 101
10 101
10 101
10 101
10 101
10 101
10 102
10 102
10 102
10 102
10 103
10 104
10 104
10 104
10 105
11 106
11 106
11 106
11 106
11 106
11 106
11 106
11 106
11 106
11 107
11 107
11 108
11 108
11 108
11 108
11 108
11 108
11 108
;

proc sql;
   create table want as
   select * from have
   where herd in 
   (select distinct herd from have group by herd having count(distinct cow_id) >= 5);
quit;
                  
Barkamih
Pyrite | Level 9

My dataset over million, how I can put them in the code, I'm sorry about this but I'm new with SAS.

REGARDS 

Reeza
Super User

You don't.

 

You reference your SAS data set. We use cards/datalines to create example data to test the code.

 


@Barkamih wrote:

My dataset over million, how I can put them in the code, I'm sorry about this but I'm new with SAS.

REGARDS 


 

pau13rown
Lapis Lazuli | Level 10

proc sort data=....;

by herd cow_id;

run;

 

data ....

retain count;

set ....;

by herd cow_id;

if first.herd then count=1;

if first.cow_id then count=count+1;

run;

 

does that work?

Barkamih
Pyrite | Level 9

NOT WORKING 

PaigeMiller
Diamond | Level 26

@Barkamih wrote:

NOT WORKING 


When someone says "NOT WORKING", this gives us NO information on what has gone wrong, and so there's no way for us to help further.

 

Give us information. 

 

Show us your code

 

Show us your SASLOG

 

Show us a part of the results that indicate a problem.

--
Paige Miller
Barkamih
Pyrite | Level 9

Sorry about this, I was so stressful because I have a deadline soon. 

 

this id the error 

 

1273
1274
1275
1276 data FATD
1277
1278 retain count;
------
56
ERROR 56-185: RETAIN is not allowed in the DATA statement when option DATASTMTCHK=COREKEYWORDS.
Check for a missing semicolon in the DATA statement, or use DATASTMTCHK=NONE.

1279
1280 set FATD;
1281
1282 by herd cow_id;
1283
1284 if first.herd then count=1;
1285
1286 if first.cow_id then count=count+1;
1287
1288 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

 

Reeza
Super User
You're missing a semicolon.
PaigeMiller
Diamond | Level 26

@Barkamih wrote:


1276 data FATD
1277
1278 retain count;


Put a semi-colon after data FATD

--
Paige Miller
Barkamih
Pyrite | Level 9

Yes I found  that 

but Nothing change in data as this log showing 

 

408 proc sort data=FATD;
409
410 by herd cow_id;
411
412 run;

NOTE: There were 1519417 observations read from the data set WORK.FATD.
NOTE: The data set WORK.FATD has 1519417 observations and 55 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 3.11 seconds
cpu time 1.86 seconds


413
414
415
416 data FATD;
417
418 retain count;
419
420 set FATD;
421
422 by herd cow_id;
423
424 if first.herd then count=1;
425
426 if first.cow_id then count=count+1;
427
428 run;

NOTE: There were 1519417 observations read from the data set WORK.FATD.
NOTE: The data set WORK.FATD has 1519417 observations and 55 variables.
NOTE: DATA statement used (Total process time):
real time 9.37 seconds
cpu time 1.71 seconds

 

 

 

novinosrin
Tourmaline | Level 20

@Barkamih  May i ask why haven't you tried @PeterClemmensen sql solution in the first place ?

 

 

Barkamih
Pyrite | Level 9

That what I used to solve my issue!!!!!!

novinosrin
Tourmaline | Level 20
data have;
input herd                     cow_id;
cards;
10                           101
10                           101
10                           101
10                           101
10                           101
10                           101 
10                           101
10                           102
10                           102
10                           102
10                           102
10                           103
10                           104
10                           104
10                           104
10                           105
11                           106
11                           106
11                           106
11                           106
11                           106
11                           106
11                           106
11                           106
11                           106
11                           107
11                           107
11                           108
11                           108
11                           108
11                           108
11                           108
11                           108
11                           108 
;

proc sql;
create table want as
select *
from have
group by herd
having  count(distinct cow_id)>=5;
quit;
Barkamih
Pyrite | Level 9

Thank you every one. 

my  best regards to all of you 

 

Brakmih 

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
  • 14 replies
  • 2014 views
  • 7 likes
  • 6 in conversation