Keep and printout DUPLICATE rows

[JohnChen_TW]

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

[Astounding · Accepted solution]

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

[JohnChen_TW]

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

[Astounding]

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.

[JohnChen_TW]

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?

[Astounding]

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.

[JohnChen_TW]

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

JC