topic Re: Suppress sort when getting distinct? in SAS Programming

Suppress sort when getting distinct?

sustagens — Fri, 27 Dec 2019 09:23:54 GMT

How do I get the distinct values of a variable without it being sorted alphabetically for me?
I would like to retain the order of the values in the initial table.

Re: Suppress sort when getting distinct?

Kurt_Bremser — Fri, 27 Dec 2019 09:37:48 GMT

Set a continous counter (derived from _N_ in a data step) before the sql, and use it in an order by clause.

Re: Suppress sort when getting distinct?

KachiM — Fri, 27 Dec 2019 10:19:43 GMT

Hi @sustagens

If your aim is to get a list of Distinct values of a Variable without sorting and changing the order of values, you use Proc Sql. To get the list of distinct AGEs from SASHELP.CLASS use:

proc sql noprint;
   select distinct (Age) into :dList separated by ' '
      from sashelp.class
   ;
quit;
%put &dList;

Similarly, you can do for a Character Variable.

Re: Suppress sort when getting distinct?

Kurt_Bremser — Fri, 27 Dec 2019 11:44:31 GMT

And if you can fit the variable values for de-duplication in memory, a single data step with a hash can do it.

data want;
set have;
if _n_ = 1
then do;
  declare hash lookup;
  lookup.definekey("key");
  lookup.definedone();
end;
if lookup.check() ne 0;
rc = lookup.add();
drop rc;
run;

Where key is the variable used for de-duplication. If needed, more variables can be added in the definekey() method.

Code is not tested, as I'm posting from my tablet.

Re: Suppress sort when getting distinct?

Ksharp — Fri, 27 Dec 2019 11:36:43 GMT

Or you could try PROC FREQ + ORDER= option.

data class;
set sashelp.class;
run;

proc freq data=class noprint order=data;
table age/out=want nopercent nocum;
run;