topic Re: Count with Where in SAS Enterprise Guide

Count with Where

ismahero2 — Thu, 09 Jan 2014 00:05:57 GMT

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?

Re: Count with Where

overmar — Thu, 09 Jan 2014 00:30:37 GMT

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;

Re: Count with Where

ismahero2 — Thu, 09 Jan 2014 00:53:05 GMT

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?

Re: Count with Where

overmar — Thu, 09 Jan 2014 00:59:34 GMT

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

Re: Count with Where

ismahero2 — Thu, 09 Jan 2014 02:37:41 GMT

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;

Re: Count with Where

overmar — Thu, 09 Jan 2014 02:44:06 GMT

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

Re: Count with Where

ismahero2 — Thu, 09 Jan 2014 03:05:19 GMT

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.

Re: Count with Where

overmar — Thu, 09 Jan 2014 03:11:19 GMT

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;

Re: Count with Where

Patrick — Thu, 09 Jan 2014 09:28:43 GMT

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;

Re: Count with Where

overmar — Thu, 09 Jan 2014 16:22:55 GMT

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?

Re: Count with Where

WilliamWei — Thu, 09 Jan 2014 16:43:25 GMT

You guys like SQL so much? How about this:

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

Re: Count with Where

Astounding — Thu, 09 Jan 2014 19:17:20 GMT

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.

Re: Count with Where

overmar — Thu, 09 Jan 2014 19:51:30 GMT

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