turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- How do I count (and sum) occurrences of a variable...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-08-2017 02:42 PM

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

Accepted Solutions

Solution

11-08-2017
03:19 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Revere2323

11-08-2017 02:56 PM

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

All Replies

Solution

11-08-2017
03:19 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Revere2323

11-08-2017 02:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

11-08-2017 03:03 PM - edited 11-08-2017 03:06 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Revere2323

11-08-2017 03:59 PM

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.