04-28-2014 06:59 PM
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;
04-28-2014 07:12 PM
You can unique variable by doing a union with same table. I am not sure which is efficient. But can be done as follows
input a $ b $;
select * from abc
select * from abc
04-28-2014 09:40 PM
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:
04-29-2014 05:25 AM
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.
04-30-2014 11:39 AM
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...
05-01-2014 05:31 PM
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.