How do I count (and sum) occurrences of a variable that satisfies certain requirements

[Revere2323]

Hi all,

I am trying to count the number of times an occurrence happened in a state, and thus get an output that lists the number of times an event satisfies for that state.

So far I have used:

proc freq;
where (Type='UN' or 'ST') AND (mass>=3) AND
(V27=15 or V27=14 or V27=13 or V27=12 or V27=11);
tables state / out=DATA.new(drop=percent);
run;

What I want is a count of the number of times a state has an occurrence where the variable "type" is UN or ST, mass (count)>=3, and 11=<V27<=25. When I use the statement above it gives me incorrect answers so I have no idea what it is counting. It would be super nice if it placed a 0 in the table if the event is not counted.

The final table I would like would look like:

AL 1

AK 2

AZ 0

CA 2

etc.

Can someone give me a hand? I am on SAS 9.4

[ballardw · Accepted solution]

You would have to provide example data and the expected count to really know what is going "wrong".

Most likely cause: (Type='UN' or 'ST')  where you meant: Type='UN' or Type='ST'  or better Type in ('UN' 'ST')

Note the "Invalid numeric data, 'ST' notes in the log from running this code:

data example;
   input type $;
   result = (Type='UN' or 'ST');
datalines;
UN
ST
BX
;
run;

The Result would be 1 where true and 0 where false.

One possibility is that your V27 variable has values that are not integers.

BTW SAS will accept a condition such as

(11 le V27 le 15) where the le is short for Less than or equal (assuming you meant 15 as that is the largest value you show in your where statement)

or if you want to compare to a list of specific values:

V27 in (11, 12, 13, 14, 15) which can also be V27 in (11 : 15)  <= ONLY for integers

[Revere2323]

Hi, that actually makes it count correctly! I think that the "Type" not being written twice was the issue.

V27 are integers.

Just a follow up question: how could I make it list 0 if there is not an observation that fits the criteria for that state?

[ballardw]

If a value of State is not in your data set the only way I know is to use a procedure that allows use of a format with all the expected values

and the Preloadfmt option such as tabulate.

data have;
   input state $;
datalines;
AK
AK
AL
CA
CA
CA
;

proc format library=work;
value $expected (notsorted)
AL='AL'
AK='AK'
AZ='AZ'
CA='CA'
;


proc tabulate data=have out=want missing;
   class state /  preloadfmt order=data;
   format state $expected.;
   table state='',n
    /printmiss  box=state misstext='0';
run;

However the output data set will have missing instead of 0 as there is actually nothing to count. You could use a data step to replace the missing with 0 or use a format to display missing as 0 depending on what you need the 0 for. Replace if it is to be used in calculations (of course don't use 0 as a denominator unless you want errors) or format to display.