Selecting distinct Combinations of two variables

[fengyuwuzu]

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

[Kurt_Bremser]

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.

[fengyuwuzu]

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?

[LinusH]

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?

[data_null__]

---

@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..

[RW9]

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.

[data_null__ · Accepted solution]

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;

[data_null__]

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;

[fengyuwuzu]

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