BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Revere2323
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Fluorite | Level 6

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
Super User

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.

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 2773 views
  • 2 likes
  • 2 in conversation