topic Re: Selecting distinct Combinations of two variables in SAS Programming

Selecting distinct Combinations of two variables

fengyuwuzu — Wed, 17 Feb 2016 14:35:04 GMT

Hello, I want to select distinct cases with two variables, distinct "combination" of the two variables. One way is using data step, but do I need to have a sort step before this?

data new_data;
set my_old_data;  
by ID date;
if first.ID and first.date;
run;

another way is to use proc sql, but how to keep all other variables?

proc sql;
create new_table as
select distinct ID, data 
from my_old_data;
quit;

Thanks

Re: Selecting distinct Combinations of two variables

Kurt_Bremser — Wed, 17 Feb 2016 14:45:09 GMT

If you use BY in a data step, the dataset needs to be sorted according to the BY statement.

Once you have the distinct combinations and want the remaining variables you need to make a decision which records to keep and which to drop.

Re: Selecting distinct Combinations of two variables

RW9 — Wed, 17 Feb 2016 14:50:20 GMT

Its depends on what your logic is. In the datastep you provide yes you will have to sort the data, in the sql you may not get the same result as it is up to observation order which comes out as the distinct value. First you need to define what variables should come out of the distinct, and example:
A B OTHER

1 1 ABC

1 1 DEF

1 1 EFG

Now if I do a distinct on the above just for A/B then the output is 1 1. Simple. But if I want the other data, I need to define which one of the available rows to take, should it be:
1 1 ABC or 1 1 EFG? Do you see the problem? With the datastep approach you will have sorted the data first, and it will take the first from the sort.

Post example of all your data and how you want them ordered.

Re: Selecting distinct Combinations of two variables

fengyuwuzu — Wed, 17 Feb 2016 14:52:15 GMT

Thanks. Sort first is indeed necessary in the data step. I think in data step all other variables are automatically kept if I do not specify keep or drop.

However, if I use the other method, ie proc sql, how to keep other varaibles which selecting the distinct combination of ID & date?

Re: Selecting distinct Combinations of two variables

data_null__ — Wed, 17 Feb 2016 16:53:59 GMT

Consider this application of PROC SUMMARY to find the distinct values of age*sex and the observation associated with the first occurrence.

proc print data=sashelp.class;
   run;
proc summary data=sashelp.class nway;
   class age sex;
   output out=distinct(drop=_type_) idgroup(obs out[1](name height weight)=);
   run;
proc print;
   run;

Re: Selecting distinct Combinations of two variables

data_null__ — Wed, 17 Feb 2016 15:11:18 GMT

Maybe this example is closer to what you actually wan to do. Here SEX is like your ID variable and AGE is like DATE. Just as if they had been sorted by SEX and AGE.

proc summary data=sashelp.class nway;
   class sex;
   output out=distinct(drop=_type_) idgroup(min(age) obs out[1](age name height weight)=);
   run;

Re: Selecting distinct Combinations of two variables

LinusH — Wed, 17 Feb 2016 15:22:32 GMT

SQL is not really built for that. It is columns based, and this is more of a row (observation) based query.

Unless you wish to do some fancy calculation of which values to keep from the other variables/columns, isn't this a simple PROC SORT NODUPKEY?

Re: Selecting distinct Combinations of two variables

data_null__ — Wed, 17 Feb 2016 16:52:27 GMT

@LinusH wrote:

SQL is not really built for that. It is columns based, and this is more of a row (observation) based query.

Unless you wish to do some fancy calculation of which values to keep from the other variables/columns, isn't this a simple PROC SORT NODUPKEY?

To get what I think @fengyuwuzu wants would take two sorts one to sort by ID and DATE and another to de-dup to distinct ID where the NODUPKEY would be used..

Re: Selecting distinct Combinations of two variables

fengyuwuzu — Wed, 17 Feb 2016 18:40:50 GMT

Thank you all the replies. So nice to read all your replies. Learned a lot.