topic Re: Distinct values for every column in my dataset? in SAS Programming

Distinct values for every column in my dataset?

Ody — Thu, 22 Jan 2015 17:08:18 GMT

Hello,

I have a large table and I'd like to look at the distinct values in each column. Short of repeatedly repeating this statement for each column, what is a better way of accomplishing this task. I'm still learning loops which I think would be applicable here.:

proc sql;

create table Chk as

select distinct

Column_Name

from Table;

Quit;

Appreciate any help. Thanks!

Re: Distinct values for every column in my dataset?

billfish — Thu, 22 Jan 2015 19:24:16 GMT

A solution is as follows

/********************************************************/
/*** sample dataset with numeric and character fields ***/
/********************************************************/
data t_have(keep=a1-a3 b1-b3);
array a(3);
array b(3) $;
do i=1 to 10;
   do j=1 to 3;
      a(j) = int(10*ranuni(3));
    if a(j) in (0,1,2) then b(j) = 'a';
      else if a(j) in (3,4,5) then b(j) = 'b'; else b(j)='c';
   end;
   output;
end;
run;

/*** DATA STEP producing 2 tables ***/
/*** t_numb = unique set of (numeric field, numeric value) ***/
/*** t_char = unique set of (character field, character value) ***/
data _null_;
   set t_have nobs=n_last;
   array NN(*) _numeric_;
   array CC(*) _character_;

   if _N_=1 then do;
      declare hash h_num(multidata:'N', ordered:'yes');
      h_num.definekey('xname','xvalue');
      h_num.definedone();

      declare hash h_chr(multidata:'N', ordered:'yes');
      h_chr.definekey('yname','yvalue');
      h_chr.definedone();
   end;

   do i = 1 to dim(NN);
      xname=vname(NN(i));
      xvalue = NN(i);
      if h_num.check() then h_num.add();
   end;

   do i = 1 to dim(CC);
      yname=vname(CC(i));
      yvalue = CC(i);
      if h_chr.check() then h_chr.add();
   end;

   if _N_ = n_last then do;
      h_num.output(dataset: 't_numb');
      h_chr.output(dataset: 't_char');
   end;

run;

Re: Distinct values for every column in my dataset?

art297 — Thu, 22 Jan 2015 19:45:37 GMT

Do you need to create a file or would having a listing meet your needs? If the latter, you can achieve what you want with:

proc freq data=table;

run;

Re: Distinct values for every column in my dataset?

Ody — Thu, 22 Jan 2015 20:36:56 GMT

Thanks for the assistance billfish.

Arthur, a list is fine.

Thanks!