BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JohnChen_TW
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

6 REPLIES 6
Astounding
PROC Star

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
Quartz | Level 8
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
PROC Star

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
Quartz | Level 8
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
PROC Star

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
Quartz | Level 8
Okay, thanks for all your support!!!
It's really helpful for me!

JC

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 1069 views
  • 1 like
  • 2 in conversation