how can i count number of character value in proc report

[ESSALAHBENNANI]

HI,

I want to count number of bulls by farm and by year.

my data is :

farm       year      bulls

A            2012        x

A            2013        y

A            2014        x

B            2015         k

B            2013        n

I used proc report : 

proc report data=work.H nowd;
columns Farm year bulls;
define Farm/ group;
define year/ group;
define bulls / analysis n "Number of bulls";

run;

but it dosen't run because the var 'bulls' is not a numeric var.

thx

[ESSALAHBENNANI]

HI,

I think with proc sql is more easy :

proc sql;
create table nbr_F as
select Ferme, Annee_IA, count(num_national) as nbr_F
from Lait_plu.H
group by Ferme, Annee_IA;
quit;

[jklaverstijn]

Can you show what your expected output would look like. Much like proc report I also have no straigtforward idea of how to add / count that character variable " bulls". One thing I can imagine is that you would like a count of distinct values of bulls. But that would be guessing. 

Regards, Jan.

[ESSALAHBENNANI]

Farm year nbr_bulls
A 2012 2739
A 2013 3799
A 2014 3678
A 2015 1968
B 2012 481
B 2013 1176
B 2014 1192
B 2015 1212
C 2012 193
C 2013 522
C 2014 454
C 2015 449
D 2012 404
D 2013 430
D 2014 426
D 2015 416
E 2012 822
E 2013 831
E 2014 856
E 2015 1008

Yes I want a count of distinct values.
thx

[jklaverstijn · Accepted solution]

Distinct counts can be easily obtained with SQL. You could code a CREATE TABLE and use that table in PROC REPORT for presentation purposes.

data h;
   input farm $ year bulls $;
   cards;
A 2012 x
A 2012 p
A 2013 y
B 2013 x
B 2014 k
B 2015 n
;
proc sql;
   select farm, year, count(distinct bulls) as nbr_bulls
   from h
   group by farm, year;
quit;

[ESSALAHBENNANI]

thx you verry much

[Mark2010]

Is converting 'bulls' to numeric in a DATA Step first an option for you?

SAS Sample for the INPUT and PUT functions. http://support.sas.com/kb/24/590.html

[ESSALAHBENNANI]

no I don't think so
thx

[Reeza]

You can also use proc tabulate if you want to customize your report, and as you've posted Proc SQL is an option.