## Distinct values for every column in my dataset?

Frequent Contributor
Posts: 93

# Distinct values for every column in my dataset?

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!

Contributor
Posts: 52

## Re: Distinct values for every column in my dataset?

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);
end;

do i = 1 to dim(CC);
yname=vname(CC(i));
yvalue = CC(i);
end;

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

run;

PROC Star
Posts: 8,164

## Re: Distinct values for every column in my dataset?

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;

Frequent Contributor
Posts: 93

## Re: Distinct values for every column in my dataset?

Thanks for the assistance billfish.

Arthur, a list is fine.

Thanks!

Discussion stats
• 3 replies
• 274 views
• 1 like
• 3 in conversation