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!
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;
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;
Thanks for the assistance billfish.
Arthur, a list is fine.
Thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.