topic Re: Keep and printout DUPLICATE rows in SAS Programming

Keep and printout DUPLICATE rows

JohnChen_TW — Wed, 29 Mar 2017 15:41:22 GMT

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!

Re: Keep and printout DUPLICATE rows

Astounding — Wed, 29 Mar 2017 15:47:44 GMT

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

Re: Keep and printout DUPLICATE rows

JohnChen_TW — Wed, 29 Mar 2017 16:21:59 GMT

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

Re: Keep and printout DUPLICATE rows

Astounding — Wed, 29 Mar 2017 16:31:35 GMT

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.

Re: Keep and printout DUPLICATE rows

JohnChen_TW — Wed, 29 Mar 2017 16:53:50 GMT

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?

Re: Keep and printout DUPLICATE rows

Astounding — Wed, 29 Mar 2017 17:00:02 GMT

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.

Re: Keep and printout DUPLICATE rows

JohnChen_TW — Wed, 29 Mar 2017 17:03:19 GMT

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

JC