topic Re: proc sort data: nodup/nodupkey OR proc sql in SAS Enterprise Guide

proc sort data: nodup/nodupkey OR proc sql

jimksas — Mon, 28 Apr 2014 22:59:03 GMT

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;

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

kiranv_ — Mon, 28 Apr 2014 23:12:18 GMT

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

sam hello

;

run;

proc sql;

select * from abc

union

select * from abc

;

run;

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

kiranv_ — Mon, 28 Apr 2014 23:33:35 GMT

proc sql;

select distinct * from table;

quit;

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

SASKiwi — Tue, 29 Apr 2014 01:40:38 GMT

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;

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

LinusH — Tue, 29 Apr 2014 09:25:02 GMT

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.

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

jimksas — Wed, 30 Apr 2014 15:39:16 GMT

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!

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

Reeza — Wed, 30 Apr 2014 16:19:43 GMT

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

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

nrose — Thu, 01 May 2014 21:31:10 GMT

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