Desktop productivity for business analysts and programmers

How to use nodupkey in query builder

Reply
Contributor
Posts: 70

How to use nodupkey in query builder

Hello ,

can anyone help in using of nodupkey by id in sas eg query builder

please need it very fast.

PROC Star
Posts: 402

Re: How to use nodupkey in query builder

Why not use the sort task instead, and use NODUPKEY in that?

//Fredrik

Super User
Posts: 24,026

Re: How to use nodupkey in query builder

You use DISTINCT in Query Builder.

There's a check box at the bottom to 'Select Distinct Rows Only' 

 

Respected Advisor
Posts: 4,797

Re: How to use nodupkey in query builder

@Reeza

I know you know this: A SQL DISTINCT return unique rows and though is not necessarily the same like a Proc Sort NODUPKEY which will return unique key combination and drops other observations even if there are differences between other non key variables.

Super User
Posts: 24,026

Re: How to use nodupkey in query builder

Doesn't that partly depend on where you place the distinct?

 


Patrick wrote:

@Reeza

I know you know this: A SQL DISTINCT return unique rows and though is not necessarily the same like a Proc Sort NODUPKEY which will return unique key combination and drops other observations even if there are differences between other non key variables.


 

Respected Advisor
Posts: 4,797

Re: How to use nodupkey in query builder


Reeza wrote:

Doesn't that partly depend on where you place the distinct?

 


Patrick wrote:

@Reeza

I know you know this: A SQL DISTINCT return unique rows and though is not necessarily the same like a Proc Sort NODUPKEY which will return unique key combination and drops other observations even if there are differences between other non key variables.


 


@Reeza Not sure but I might be missing something. Using below code can you please demonstrate how you would use a DISTINCT to get the same result in want2 than what's in want1.

data have;
  do var=5 to 8;
    do id=1 to 3;
      output;
    end;
  end;
run;

proc sort data=have out=want1 nodupkey;
  by id;
run;

proc sql;
  create table want2 as
  select distinct *
  from have
  ;
quit;

 

Super User
Posts: 24,026

Re: How to use nodupkey in query builder

In this particular case I don't think you can. It depends on the query, and I was thinking primarily of cases where you're doing summary queries and have a either a distinct or count(distinct) which operates differently. 

 

I think you're correct, in that NODUPKEY and DISTINCT are not the same, but I think NODUPRECS and DISTINCT are the same. However, SQL doesn't give you good control over the record it removes, whereas PROC SORT or a DATA STEP will give you more control. 

Respected Advisor
Posts: 4,797

Re: How to use nodupkey in query builder

@Reeza

Agree with all you write except for NODUPRECS which in my opinion is a sort option which should get depreciated. Check out below:

data sample;
  input id var;
  datalines;
1 1
1 1
1 2
1 1
;
run;

proc sort data=sample out=result noduprecs;
  by id;
run;

proc print data=result;
run;

Capture.JPG

 

Super User
Posts: 24,026

Re: How to use nodupkey in query builder

I'm aware of that, NODUPRECS requires data side by side, i.e. the double sort required for appropriate usage.

I think it should mimic the SQL DISTINCT, not be deprecated. 

 

Respected Advisor
Posts: 4,797

Re: How to use nodupkey in query builder

@Reeza You get the Distinct via NODUPKEY and sort BY _ALL_

 

Ask a Question
Discussion stats
  • 9 replies
  • 399 views
  • 5 likes
  • 4 in conversation