Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Simple way to remove the missings when you count distinct?

Reply
New Contributor
Posts: 2

Simple way to remove the missings when you count distinct?

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

Grand Advisor
Posts: 9,748

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

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

Regular Contributor
Posts: 196

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

Hi Santiago,

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

Teja Surapaneni.

New Contributor
Posts: 2

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

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:

Modelo Tabla.PNG

Esteemed Advisor
Posts: 5,989

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

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

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

4    3    3
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 5,989

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 37

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

Hi

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

New Contributor
Posts: 2

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

[ Edited ]

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

New Contributor
Posts: 2

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

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

 

sasva.png

Post a Question
Discussion Stats
  • 8 replies
  • 1025 views
  • 3 likes
  • 6 in conversation