topic Re: Simple way to remove the missings when you count distinct? in SAS Visual Analytics

Simple way to remove the missings when you count distinct?

starnassi — Thu, 19 Feb 2015 20:50:50 GMT

Hi, this might be a really dumb question, but I haven't found a way around this one.

Whenever I create distinct values from a category, it counts all the missings from that category as 1, asi it should. However, I can't think of a SIMPLE way of removing those 'missing' values from the distinct calculation (e.g: Column A values values: 1, 2, 3, missing, missing. Distinct by group (column A) = 4, I would like it to be 3). I can imagine some workarounds, marking as 1 or 0 if a certain value has certain properties and then do a sum by group, but that is what I'm trying to avoid. Has anybody come across this and can provide a solution?

Thanks in advance!

Santiago

Re: Simple way to remove the missings when you count distinct?

ballardw — Thu, 19 Feb 2015 23:56:22 GMT

I think this may want a small example start data set and what the final results should look like.

Re: Simple way to remove the missings when you count distinct?

TejaSurapaneni — Tue, 24 Feb 2015 09:22:08 GMT

Hi Santiago,

you can use Data Source Filter, it remove blank or missing values from table level.

Teja Surapaneni.

Re: Simple way to remove the missings when you count distinct?

Kurt_Bremser — Tue, 24 Feb 2015 11:45:29 GMT

What keeps you from using a where condition to exclude the missing values?

Re: Simple way to remove the missings when you count distinct?

Karthikeyan — Tue, 24 Feb 2015 13:33:45 GMT

Where are you seeing this , in a SAS dataset? if you perform a proc sql over a SAS dataset, then it'll exclude the null/missing values while using Count(). If you are querying from a Database, then you may want to use a function to handle the NULLs before you can perform a count().

you can use ISNULL() , IFNULL() or COALESCE() etc depending on which function your database is supporting.

Thanks

Karthik

Re: Simple way to remove the missings when you count distinct?

starnassi — Tue, 24 Feb 2015 14:46:51 GMT

Hi guys, thank you all for the answers so far.

I could remove the missings from the dataset, but given the workflow within the company, it is a request someone else should do and this takes a long time. To avoid this is why I'm looking for a for a way to do this in Visual Analytics using only calculated elements and filters. Sadly, I cannot use a where clause when making a new aggregated measure (distint by group in this case). BTW, I'm aware that this might not be possible, I'm just wondering if someone had a handy solution without modifying the dataset.

Here's an example of the table I'm working with:

Re: Simple way to remove the missings when you count distinct?

Kurt_Bremser — Wed, 25 Feb 2015 06:45:01 GMT

Well, this:

data have;

infile cards;

input

category :$1.

pid :1.

cid :1.

sid :1.

;

cards;

A 1 1 1

A 2 1 .

A 3 2 .

A . . .

B 2 1 2

B 3 2 .

B 4 3 3

;

run;

proc sql;

create table want as

select

count(distinct pid) as pid, count(distinct cid) as cid, count(distinct sid) as sid

from have;

quit;

gives me exactly the result you wanted:

pid

cid

sid

----------------------------

Re: Simple way to remove the missings when you count distinct?

McGoo — Sun, 15 May 2016 08:56:52 GMT

Hi starnassi,

Did you ever work out a solution, I just came across a problem that now has this problem too.

I was thinking you could do the distinct count -1 but not sure I can be sure there will always be a MISSING value.

I too need to do in SASVA rather than data prep as I am looking to do the distinct count on an calculated item with an IF ELSE statement.

Thanks

Re: Simple way to remove the missings when you count distinct?

McGoo — Mon, 16 May 2016 01:47:54 GMT

Worked it out.. This just checks to see if there is 1 or more missing using same aggregate and the -1 if there is.

Re: Simple way to remove the missings when you count distinct?

mcgarrick — Wed, 12 Jul 2017 00:36:01 GMT

works perfectly. Thankyou!