BookmarkSubscribeRSS Feed
anirudhs
Obsidian | Level 7

Hello ,

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

please need it very fast.

9 REPLIES 9
FredrikE
Rhodochrosite | Level 12

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

//Fredrik

Reeza
Super User

You use DISTINCT in Query Builder.

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

 

Patrick
Opal | Level 21

@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
Super User

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.


 

Patrick
Opal | Level 21

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

 

Reeza
Super User

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. 

Patrick
Opal | Level 21

@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

 

Reeza
Super User

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. 

 

Patrick
Opal | Level 21

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

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 9 replies
  • 3320 views
  • 5 likes
  • 4 in conversation