topic distinct on selected variables and remaining as it is in SAS Programming

distinct on selected variables and remaining as it is

alexdsa310 — Wed, 29 Mar 2017 10:19:25 GMT

is there any way in proc sql if i can select distinct on few varibles and have remaining as it is.

as below i do not want distinct on b.term,b.acce and need for the rest

proc sql;
create table _values_ as
select (distinct b.table,b.column,b.origin,b.originwhere,b.algorithm), b.term,b.acce
from vcol a right join source_columns b
on a.origin=b.origin

quit;

any suggestion please

Re: distinct on selected variables and remaining as it is

Kurt_Bremser — Wed, 29 Mar 2017 10:30:07 GMT

How about

proc sql;
create table _values_ as
select b.table,b.column,b.origin,b.originwhere,b.algorithm, b.term,b.acce
from vcol a right join source_columns b
on a.origin=b.origin
group by b.table,b.column,b.origin,b.originwhere,b.algorithm
;
quit;

Re: distinct on selected variables and remaining as it is

alexdsa310 — Wed, 29 Mar 2017 10:33:27 GMT

No need to mention distinct? sorry to ask

Re: distinct on selected variables and remaining as it is

Kurt_Bremser — Wed, 29 Mar 2017 11:07:26 GMT

As soon as the distinct does not extend over all columns, it becomes meaningless, as all rows from the dataset will be included anyway. Play around with the concept for a while, and you'll see what I mean.