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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.