Hi @DrAbhijeetSafai ,
I know you've already received good responses for this. However, I decided to share my approach anyway which uses the max function in proc SQL.
proc datasets lib=work kill nolist nowarn; quit;
data test;
set sashelp.class;
call missing(age);
if _n_ = 1 then call missing(sex);
sex2='';
run;
proc contents data=test out=contents;
run;
data _null_;
set contents end=eof;
length COLS $32767;
retain COLS '';
if _N_ = 1 then do;
call execute ('PROC SQL;');
call execute ('CREATE TABLE CHECK AS SELECT');
end;
COLS = catx(", ", COLS, catx(" ", cats('max(',name,')'), "as", name));
if EOF then do;
call execute (COLS);
call execute ('FROM TEST;');
call execute ('QUIT;');
end;
run;
/*Drop columns if they are missing on all rows*/
data _null_;
set check end=eof;
array n [*] _NUMERIC_;
array c [*] _CHARACTER_;
length COLS $200;
call execute ('proc sql;');
call execute ('ALTER TABLE TEST');
call execute ('DROP COLUMN');
do i=1 to dim(n);
if missing(n[i]) then COLS = catx(", ", COLS, vname(n[i]));
end;
do i=1 to dim(c);
if missing(c[i]) then COLS = catx(", ", COLS, vname(c[i]));
end;
call execute(COLS);
call execute(';quit;');
run;
... View more
