BookmarkSubscribeRSS Feed
suraestar
Calcite | Level 5

Dear All,


I need the NODUPKEY functionality in PROC SQL Similar to PROC SORT.

The below code give you unique values of MAKE from the CARS dataset with ALL VARIABLES.
proc sort data=sashelp.cars out=cars nodupkey;
by make;
run;

whereas in PROC SQL the distinct function will give you only the MAKE variable in the output dataset.
proc sql;
create table cars as
select distinct make from sashelp.cars;
quit;


Is it possible to get UNIQUE values + All other variables in PROC SQL.

Note: We can use the monotonic function to get the above thing. but it is not the efficient way.

Could you please share your ideas.

thanks
Surendar

9 REPLIES 9
Patrick
Opal | Level 21

I would have to ask what's the value of having additional variables which are just picked from one of the rows with multiple keys. What are you going to do with it?

To get a similar result using Proc SQL your approach using monotonic() appears to be to most efficient way from the amount of code you have to write.

proc sql;

  create table test(drop=n) as

  select *, monotonic() as n

  from sashelp.cars

  group by make

  having max(n) = n

  ;

quit;

Else you would have to use the max() or min() function on every variable:

proc sql;

  create table test as

  select make, max(model) as model, max(.....

  from sashelp.cars

  group by make

  ;

quit;

Ksharp
Super User

You need make a id variable on your own.

ods listing close;
ods output sql_results=temp;
proc sql number;
select * from sashelp.cars;
quit;
proc sql;
create table want as
 select * from temp
  group by make
   having row=min(row);
quit;
ods listing;

Ksharp

suraestar
Calcite | Level 5

Hi Patrick,

My point is that I am filtering the dataset which has no duplicate records based on one or more key variable in it .  Ofcourse I need the other variables too in my target dataset.This can be done in PROC SORT - NODUPKEY statement..

I need a similar to statement in proc sql to do the job..

BTW if we use the monotonic funcation along with MAX.. It takes more time than PROC SORT and also we will end up with following note

NOTE: The query requires remerging summary statistics back with the original data

Haikuo
Onyx | Level 15

If efficiency is your concern, consider using Hash() objects.

Haikuo

suraestar
Calcite | Level 5

Hi.  Yes you are right I was looking for the efficiency. But I m not aware of hash concepts.  Can you please guide me about hash.

Haikuo
Onyx | Level 15

Play with this example code to see how much you can get from it, it basically output two records based on key variable 'sex':

data _null_;

  if 0 then set sashelp.class;

  declare hash h(dataset: 'sashelp.class');

  h.definekey('sex');

  h.definedata(all:'yes');

  h.definedone();

  h.output(dataset:'want');

  stop;

  run;

Haikuo

Tom
Super User Tom
Super User

If PROC SORT NODUPKEY does the job why do you need to do it in PROC SQL? 

Sounds kind of like requesting how to get mean values from PROC FREQ instead of using PROC MEANS.

suraestar
Calcite | Level 5

HI All, My idea is about finding the best approach and also finding the alternate approach which is efficient.  This post will be helpful and interesting for people who thinks the either way to do the task. I had this topic in mind bcoz If I (may be we) have a task to delete duplicate records in a dataset. My mind directly chooses proc sort with nodupkey.  But I like to think or I want the alternate solution to do This.  May be I m wrong but this can help me in exploring more.  Thank you all for your comments and ideas.

Patrick
Opal | Level 21

You are NOT removing duplicate records using Proc Sort / Nodupkey unless you add all variables to the 'by' statement - and then it does the same like a SQL Distinct.

You are removing duplicate 'keys' using Nodupkey - all the other variables not in the by group are then just coming from one of the multiple records with the same key values. You have zero control which row will be selected so what's the value of having these additional variables?

There have already been many discussions in this forum around de-duplicating records.

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