The following codes proved to be working: %macro checkUnique(fileName=,col=,ID=);
%global colList;
/* get all date columns' names */
proc sql noprint;
select name into: colList separated by ' '
from dictionary.columns
where libname = 'WORK' and memname contains upcase("&fileName") and name contains upcase("&col");
quit;
/* transpose back to flat table */
data temp1(keep= ID_adj frequent position);
set work.&fileName.(rename= (&ID.=ID));
array &col._array{*} &colList.;
do i = 1 to dim(&col._array);
ID_adj = ID;
frequent = &col._array{i};
position = i;
output;
end;
format frequent date9.;
run;
/* get the frequency of each date value */
proc sql noprint;
create table temp2 as select
ID_adj,
frequent,
count(frequent) as count
/* count(distinct(frequent)) as num_unique */
from temp1
group by ID_adj, frequent;
quit;
proc sort data= temp2(where=(frequent ne .));
by ID_adj descending count descending frequent;
run;
/* get the most frequent date */
data output.&fileName.(keep= &ID. &col._adj);
set temp2;
by ID_adj descending count;
if first.ID_adj;
format frequent date9.;
rename ID_adj=&ID.;
rename frequent=&col._adj;
run;
%mend checkUnique;
%checkUnique(fileName=test,col=date,ID=id);
... View more
