Count with Where

[ismahero2]

Hi,

I have a table like this one:

Header 1	Header 2	Header 3
999	454565	5654
996	gdrgr	egdg
996	df	dgfd
999	df	xd
555	dfd	ff
444	fef	fe
333

Now, I want to create a table with only the rows in column "Header 1" where there is a distinct record.  The resulting table should be something like this:

UniqueNumbers
555
444
333

I tried to do a Count but I am having problems selecting Where Count=1.   Any ideas?

[overmar]

You need it to be in the having rather than the where. Where is for when reading things in, having can be used once things have been calculated.

select distinct (header_1) as uniquenumbers

from table

having (count(header_1)) = 1;

quit;

[ismahero2]

I think this one will work, but for now is giving me this error:

Sintax error, expecting one of the following: (, SELECT.

Any idea what I am missing?

[overmar]

What is the full code that you had written? Because right now this doesn't make much sense.

[ismahero2]

Ok, this is the exact code I have.   MyTable is the name of the new table I will create and Originaltable is the table where the data comes from.   Hope you can help me..

proc sql;

create table MyTable as;

select distinct ('header_1') as uniquenumbers

from Originaltable

having (count('header_1')) = 1;

quit;

[overmar]

I forgot to suggest an addition of group by to the earlier code and do not put a semi-colon after as, this stops sas the semi-colon should go at the end of the proc sql statement (in this case after having). This is why it was giving you a "select" error, bc you had ended the code before you had given it a select statement. This code should now sufficiently run:

proc sql;

create table MyTable as

select distinct ('header_1') as uniquenumbers

from Originaltable

group by header_1

having (count('header_1')) = 1;

quit;

Hope this helps

Robert

[ismahero2]

OK, I did it exactly like you have it and is running.  The only problems is that the table is created with the right column name, but is empty.  Any idea why?  I know I should have around 5 million records showing up, but none right now.

Thank you for your time.

[overmar]

Sorry I didn't read your code all the way through and it appears that you put ' around the column names, if you do that proc sql thinks that this is a character string rather than a column name, so take those out for the code to run, final code would be:

proc sql;

create table MyTable as

select distinct (header_1) as uniquenumbers

from Originaltable

group by header_1

having (count(header_1)) = 1;

quit;

[Patrick]

I wouldn't use "distinct". Code like below should do:

proc sql;

create table MyTable as

select header_1 as uniquenumbers

from Originaltable

group by header_1

having count(header_1) = 1;

quit;

[overmar]

Odd question, I tend to use select distinct on just about everything that I do. Many time regardless if I use a summary function, at this point I think its a force of habit. Other than the possibility of losing duplicate records am I causing any other specific issues for myself?

[WilliamWei]

You guys like SQL so much?  How about this:

Sort by header1 first, then if first.header1 and last.header1.  Using data step.

[Astounding]

overmar,

For this particular problem, you get the wrong answer if you add DISTINCT.  The COUNT will always be 1 when you examine:

group by header_1

having count (distinct header_1)=1

For every value of HEADER_1, there is only a single distinct value of HEADER_1.

[overmar]

Aha, that makes sense, I would have figured it out if I had run the code, but now I will think about it before running it. Thanks