Get overview of distinct values in dataset

The code below will make distinct values of all the variables in a dataset and list them besides each other for a better overview.

%let Delimitor = ยค;

%macro PrepareData(InLib=, Inds=);
 %let InLib = %upcase(&InLib);
 %let InDS = %upcase(&InDS);

 proc sql noprint;
  create table CON_&InDS. as
   select *
   from dictionary.columns
   where upcase(LibName) eq "&InLib." and upcase(MemName) eq "&InDS.";
  quit;

  %global Columns;

  proc sql noprint;
   select name into :Columns separated by "&Delimitor"
   from CON_&InDS.;
  quit;

  %put Columns: &Columns;

  proc sql noprint;
   create table RES_&InDS. like &InDS.;
  quit;

  data RES_&InDS. (drop = i);
   do i = 1 to 200;
    ID + 1;
    output;
   end;
   set RES_&InDS.;
  run;
%mend;

%macro MakeDistinct(InDS=,OutDS=);
 %let NumberOfColumns = %sysfunc(countw(&Columns., &Delimitor.));
 %put Number of columns: &NumberOfColumns.;

 %do J=1 %to &NumberOfColumns.;
  %let Column = %scan(&Columns., &J, &Delimitor.);
  %put Processing: &Column.;

  proc sql noprint;
   create table tmpDS as
    select distinct(&Column.) as &Column.
    from &InDS.;
  quit;

  data tmpDS;
   ID + 1;
   set tmpDS;
  run;

  data &OutDS.;
   merge &OutDS. (in=a) tmpDS (in=b);
   by id;
  run;
 %end;
%mend;

%PrepareData(InDS=sashelp, Inds=class);
%MakeDistinct(InDS=class, OutDS=Result_Class);

One thought on “Get overview of distinct values in dataset”

Leave a Reply to Melissa Cancel reply

Your email address will not be published. Required fields are marked *