DATA Step, Macro, Functions and more

Keep and printout DUPLICATE rows

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

Keep and printout DUPLICATE rows

Hi, I am using SAS 9.3.

I would like to keep duplicate rows only and just print them out, but not saving to another dataset.

Now, I get the code which is creating a new dataset to save duplicate rows below.

proc sql;
create table dup as 
select * 
from have
group by Birth, Sex, Height, Weight
having count(*) > 1; 
quit;

But actually, I just want to print it out. Does anyone have the suggestions?

Perhaps, if duplicate rows then print it out. Maybe.

 

Thanks, and appreciate!

 

JC

 


Accepted Solutions
Solution
‎03-29-2017 01:04 PM
Super User
Posts: 5,076

Re: Keep and printout DUPLICATE rows

I'm 99% certain on this ...

 

It's creating a table because you asked SQL to create a table.  To create a report instead, just remove one line from the program:

 

create table dup as

View solution in original post


All Replies
Solution
‎03-29-2017 01:04 PM
Super User
Posts: 5,076

Re: Keep and printout DUPLICATE rows

I'm 99% certain on this ...

 

It's creating a table because you asked SQL to create a table.  To create a report instead, just remove one line from the program:

 

create table dup as

Contributor
Posts: 56

Re: Keep and printout DUPLICATE rows

Yes, it works after I removed "create table dup as" but it will print the null result out, too.

Is there a suitable method to detect the table created by SQL is not null?

JC
Super User
Posts: 5,076

Re: Keep and printout DUPLICATE rows

Are you saying there might be no duplicates, but SQL still prints a report?  To handle that, you might as well keep your original program, and then add:

 

proc print data=dup;

run;

 

When there are no observations to print, PROC PRINT sends a message to the log but sends nothing to the output.  If you take that approach, you might switch to creating a view instead of a table:

 

create view dup as ..........

 

But if the number of duplicates is going to be small, that wouldn't be necessary.

Contributor
Posts: 56

Re: Keep and printout DUPLICATE rows

Yes, you are right! It works when I added proc print.

May I ask another question?
Is it possible to use "call symput" when creating the table with duplicate rows in SQL?
Super User
Posts: 5,076

Re: Keep and printout DUPLICATE rows

No, CALL SYMPUT can only be part of a DATA step.

 

SQL does have ways to create macro variables, using INTO :  but it can't use CALL SYMPUT.

Contributor
Posts: 56

Re: Keep and printout DUPLICATE rows

Okay, thanks for all your support!!!
It's really helpful for me!

JC
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 117 views
  • 1 like
  • 2 in conversation