BookmarkSubscribeRSS Feed
ismahero2
Obsidian | Level 7

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?

12 REPLIES 12
overmar
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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

ismahero2
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Opal | Level 21

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
Obsidian | Level 7

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
Calcite | Level 5

You guys like SQL so much?  How about this:

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

Astounding
PROC Star

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1561 views
  • 3 likes
  • 5 in conversation