Desktop productivity for business analysts and programmers

proc sort data: nodup/nodupkey OR proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

proc sort data: nodup/nodupkey OR proc sql

hello all,

i have one big (4,550,750 obs) dataset (test) with 12 variables. I want every variable with one unique variable (test_var), what is the best way to do it?

I am using nodupkey options in proc sort, is it best way to do is? OR it's PROC SQL? how can we get all variables from SAS datasets with one unique variable using PROC SQL?

proc sort data=work.test nodupkey;

by test_var;

run;


Accepted Solutions
Solution
‎09-11-2017 01:22 PM
Super User
Posts: 5,384

Re: proc sort data: nodup/nodupkey OR proc sql

Do you have duplicate records as well?

If not, does it matter which record you chose when you want a unique record?

If your data is pre-sorted - use the technique suggested by https://communities.sas.com/people/SASKiwi. If not PROC SORT NODUPKEY is you options. There is no (good) equivalent in SQL - both syntax and performance-wise.

Data never sleeps

View solution in original post


All Replies
PROC Star
Posts: 282

Re: proc sort data: nodup/nodupkey OR proc sql

hi

You can unique variable by doing a union with same table. I am not sure which is efficient. But can be done as follows

data abc;

input a $ b $;

datalines;

vijay ajay

vijay ajay

sam hello

;

run;

proc sql;

select * from abc

union

select * from abc

;

run;

PROC Star
Posts: 282

Re: proc sort data: nodup/nodupkey OR proc sql

proc sql;

select distinct * from table;

quit;

Super User
Posts: 3,233

Re: proc sort data: nodup/nodupkey OR proc sql

If your data is already sorted by test_var then you don't need to re-sort it and that will save you a lot of processing time:

data test_nodup;

  set work.test;

  by test_var;

  if first.test_var;

run;

Solution
‎09-11-2017 01:22 PM
Super User
Posts: 5,384

Re: proc sort data: nodup/nodupkey OR proc sql

Do you have duplicate records as well?

If not, does it matter which record you chose when you want a unique record?

If your data is pre-sorted - use the technique suggested by https://communities.sas.com/people/SASKiwi. If not PROC SORT NODUPKEY is you options. There is no (good) equivalent in SQL - both syntax and performance-wise.

Data never sleeps
Contributor
Posts: 55

Re: proc sort data: nodup/nodupkey OR proc sql

Yes LinusH, i have duplicate records for "test_var" variable and i don't want duplicate records...I want unique records from "test_var" variable and all/every possible value for all other variables...

Thanks!

Super User
Posts: 19,105

Re: proc sort data: nodup/nodupkey OR proc sql

I think you need to post sample data for this, preferably have and want data sets in a data step.

Frequent Contributor
Posts: 91

Re: proc sort data: nodup/nodupkey OR proc sql

If, for each group of 'test_var' you have a unique value (say 'test_var_id'), you can use this to remove duplicates using the having clause;

select * from table group by test_var having max(test_var_id) = test_var_id;

The group by clause groups by the 'test_var' variable, and the having clause picks only one record from this group. Again, this requires that there is some unique variable contained within 'test_var'.

The only reason I would try use this is if you wanted in database processing, but not all databases support the having clause in this way (e.g. SQL server). Otherwise, proc sort is your answer.

Nick

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 7872 views
  • 0 likes
  • 6 in conversation