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);
Wow, that’s a really clever way of thkniing about it!