BookmarkSubscribeRSS Feed
Ody
Quartz | Level 8 Ody
Quartz | Level 8

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!

3 REPLIES 3
billfish
Quartz | Level 8

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;

art297
Opal | Level 21

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;

Ody
Quartz | Level 8 Ody
Quartz | Level 8

Thanks for the assistance billfish.

Arthur, a list is fine.

Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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