topic Re: count repeated variables by group in SAS Procedures

count repeated variables by group

Barkamih — Wed, 20 Jun 2018 19:35:51 GMT

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

Re: count repeated variables by group

PeterClemmensen — Wed, 20 Jun 2018 19:43:54 GMT

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;

Re: count repeated variables by group

pau13rown — Wed, 20 Jun 2018 19:44:43 GMT

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?

Re: count repeated variables by group

novinosrin — Wed, 20 Jun 2018 19:48:47 GMT

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;

Re: count repeated variables by group

Barkamih — Wed, 20 Jun 2018 20:08:47 GMT

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

REGARDS

Re: count repeated variables by group

Barkamih — Wed, 20 Jun 2018 20:09:28 GMT

NOT WORKING

Re: count repeated variables by group

PaigeMiller — Wed, 20 Jun 2018 20:12:53 GMT

@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.

Re: count repeated variables by group

Reeza — Wed, 20 Jun 2018 20:16:17 GMT

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

Re: count repeated variables by group

Barkamih — Wed, 20 Jun 2018 20:16:39 GMT

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

Re: count repeated variables by group

Reeza — Wed, 20 Jun 2018 20:24:52 GMT

You're missing a semicolon.

Re: count repeated variables by group

PaigeMiller — Wed, 20 Jun 2018 20:25:27 GMT

@Barkamih wrote:

1276 data FATD
1277
1278 retain count;

Put a semi-colon after data FATD

Re: count repeated variables by group

Barkamih — Wed, 20 Jun 2018 20:28:00 GMT

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

Re: count repeated variables by group

novinosrin — Wed, 20 Jun 2018 20:34:21 GMT

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

Re: count repeated variables by group

Barkamih — Wed, 20 Jun 2018 20:51:59 GMT

Thank you every one.

my best regards to all of you

Brakmih

Re: count repeated variables by group

Barkamih — Thu, 21 Jun 2018 16:23:22 GMT

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