DATA Step, Macro, Functions and more

Selecting distinct Combinations of two variables

Accepted Solution Solved
Reply
Super Contributor
Posts: 318
Accepted Solution

Selecting distinct Combinations of two variables

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


Accepted Solutions
Solution
‎02-17-2016 01:40 PM
Respected Advisor
Posts: 3,799

Re: Selecting distinct Combinations of two variables

[ Edited ]
Posted in reply to fengyuwuzu

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;

Capture.PNG

View solution in original post


All Replies
Super User
Posts: 7,854

Re: Selecting distinct Combinations of two variables

Posted in reply to fengyuwuzu

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.

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 318

Re: Selecting distinct Combinations of two variables

Posted in reply to KurtBremser

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?

 

Super User
Posts: 5,438

Re: Selecting distinct Combinations of two variables

Posted in reply to fengyuwuzu

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 never sleeps
Respected Advisor
Posts: 3,799

Re: Selecting distinct Combinations of two variables


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

Super User
Super User
Posts: 7,988

Re: Selecting distinct Combinations of two variables

Posted in reply to fengyuwuzu

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.

Solution
‎02-17-2016 01:40 PM
Respected Advisor
Posts: 3,799

Re: Selecting distinct Combinations of two variables

[ Edited ]
Posted in reply to fengyuwuzu

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;

Capture.PNG

Respected Advisor
Posts: 3,799

Re: Selecting distinct Combinations of two variables

Posted in reply to 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;

Capture.PNG

Super Contributor
Posts: 318

Re: Selecting distinct Combinations of two variables

Posted in reply to data_null__

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

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 1907 views
  • 1 like
  • 5 in conversation