Desktop productivity for business analysts and programmers

Count with Where

Reply
Contributor
Posts: 22

Count with Where

Hi,

I have a table like this one:

Header 1Header 2Header 3
9994545655654
996gdrgregdg
996dfdgfd
999dfxd
555dfdff
444feffe
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?

Frequent Contributor
Posts: 83

Re: Count with Where

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;

Contributor
Posts: 22

Re: Count with Where

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?

Frequent Contributor
Posts: 83

Re: Count with Where

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

Contributor
Posts: 22

Re: Count with Where

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;

Frequent Contributor
Posts: 83

Re: Count with Where

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

Contributor
Posts: 22

Re: Count with Where

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.

Frequent Contributor
Posts: 83

Re: Count with Where

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;

Respected Advisor
Posts: 4,138

Re: Count with Where

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;

Frequent Contributor
Posts: 83

Re: Count with Where

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?

N/A
Posts: 1

Re: Count with Where

You guys like SQL so much?  How about this:

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

Super User
Posts: 5,372

Re: Count with Where

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.

Frequent Contributor
Posts: 83

Re: Count with Where

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

Ask a Question
Discussion stats
  • 12 replies
  • 524 views
  • 3 likes
  • 5 in conversation