Aggregate function: how to get N break excluding missing values?


New Member

I am trying to aggregate data of a variable number of measurements per day. Because it is a setting in which multiple variables are measured and not all occur in each case, there are missing values. I need to know the number of measurements that is aggregated for each different variable while aggregating over the date (the N break value).

Unfortunately, SPSS appears to count in the missing values when aggregating, giving me one N value corresponding to the total number of measurements on that day over which it aggregates, even if some (or all) variables contain missing values. Does anyone know a way to circumvent this?

Any help would be much appreciated! :)
Have you defined your "missing" value for your variables? Using a "99" is a common way to do that in SPSS. You define thse off the "Missing" column in the variable view.
I guess I'm not totally clear on what you're asking, but here goes: are you trying to identify which dates have a missing value for your variable?

The easiest way to do that is to sort your cases by the variable. From the Data menu, select Sort Cases. Then in the Sort by box, select the variable you want to find missing items for, then also add the date variable. Make sure it is set to Ascending order. Then click OK. All your missing items will be at the top of the page, in date order.

The number of missing items should be the same as you can find by doing a crosstab from the Analyze menu using the date as the row and the variable as the column. It should give you a total number of cases as well as the number of missing cases for that variable. The missing cases in this table (the middle part of the table) should be the same as the number of rows with missing items at the top of your new output.

Is that what you're looking for?