topic Re: How to use nodupkey in query builder in SAS Enterprise Guide

How to use nodupkey in query builder

anirudhs — Fri, 16 Feb 2018 05:46:24 GMT

Hello ,

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

please need it very fast.

Re: How to use nodupkey in query builder

FredrikE — Fri, 16 Feb 2018 07:33:58 GMT

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

//Fredrik

Re: How to use nodupkey in query builder

Reeza — Fri, 16 Feb 2018 16:12:27 GMT

You use DISTINCT in Query Builder.

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

Re: How to use nodupkey in query builder

Patrick — Fri, 16 Feb 2018 21:31:48 GMT

@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.

Re: How to use nodupkey in query builder

Reeza — Fri, 16 Feb 2018 21:34:51 GMT

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.

Re: How to use nodupkey in query builder

Patrick — Fri, 16 Feb 2018 21:39:51 GMT

@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;

Re: How to use nodupkey in query builder

Reeza — Sat, 17 Feb 2018 03:45:46 GMT

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.

Re: How to use nodupkey in query builder

Patrick — Sat, 17 Feb 2018 08:18:37 GMT

@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;

Re: How to use nodupkey in query builder

Reeza — Sat, 17 Feb 2018 21:33:35 GMT

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.

Re: How to use nodupkey in query builder

Patrick — Sun, 18 Feb 2018 00:27:53 GMT

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