topic Re: How to get duplicate records as output data set using proc sql only? in SAS Programming

How to get duplicate records as output data set using proc sql only?

Saikiran_Mamidi — Wed, 13 May 2020 10:25:47 GMT

Data have;

input id;

cards;

; run;

duplicate records in output dataset by using only proc sql?

like below

data want

can i get code for this ? thanks in advance.

How to get duplicate records as output dataset without using proc sort? only using proc sql?

Saikiran_Mamidi — Wed, 13 May 2020 10:22:29 GMT

Data have;

input id;

cards;

; run;

duplicate records in output dataset by using only proc sql?

like below

data want

can i get code for this ? thanks in advance.

Re: How to get duplicate records as output data set using proc sql only?

yabwon — Wed, 13 May 2020 10:29:12 GMT

You still have duplicates in Want, haven't you?

Bart

Re: How to get duplicate records as output data set using proc sql only?

mkeintz — Wed, 13 May 2020 10:32:26 GMT

What if, in addition to variable ID, you have other "satellite" variables A, B, and C, which vary within a given ID? What criterion would you use to select which records are the extra ones?

Re: How to get duplicate records as output dataset without using proc sort? only using proc sql?

LinusH — Wed, 13 May 2020 10:34:13 GMT

Why limit yourself to SQL?

What have you tried so far?

Re: How to get duplicate records as output dataset without using proc sort? only using proc sql?

yabwon — Wed, 13 May 2020 10:41:04 GMT

Something like this:

Data have;
input id;
cards;
1
1
1
2
2
44
44
44
44
; run;


proc sql;
  create table tmp as
  select id, monotonic() as i from have
  order by id
  ;

  select a.* 
  from
  tmp as a
  group by a.id
  having a.i > min(a.i)
  ;

  delete table tmp;
quit;

Not the very optimal I'd say.

Bart

Re: How to get duplicate records as output data set using proc sql only?

Kurt_Bremser — Wed, 13 May 2020 11:28:02 GMT

Not using a data step for this is outright stupid:

data want;
set have;
by id;
if first.id or not last.id;
run;

Re: How to get duplicate records as output data set using proc sql only?

Saikiran_Mamidi — Wed, 13 May 2020 11:30:11 GMT

In proc sql only ?

Re: How to get duplicate records as output data set using proc sql only?

Kurt_Bremser — Wed, 13 May 2020 11:35:15 GMT

@Saikiran_Mamidi wrote:
In proc sql only ?

Why be stupid? Isn't it much better to do the clever thing?

Re: How to get duplicate records as output data set using proc sql only?

DavePrinsloo — Wed, 13 May 2020 13:11:08 GMT

Well, if have has 600 million records but only 1 million unique key value, you would would want to avoid sorting the 600 million records.

If that is the scenario, you could read WANT sequentially only once, use a datastep with hashing to look up whether you have a match.

If so, insert the first match into the hash table (of course the other non-key saved variables might not be want you want).

At then at the end you output the hash table to a physical table.

You need to ensure that the million full rows will fit into memory. If that is not possible then

create a macro that read HAVE in n pieces using options firstobs and obs.

Then you need a final part that re-reads the reduced pieces and consolidates them into a final HAVE.