@ballardw Thanks!
Your saying that "The OUT= option of the Tables data set only outputs the LAST result." is so much correct! Thanks for pointing it out.
- Dr. Abhijeet Safai
If you are using SAS Studio, there is a Describe Missing Data task - the great thing about the Task is will show you patterns of "missingness" (example: VAR1 & VAR2 are missing on X number of observations).
@tom_grant That is interesting! Thanks for information.
- Dr. Abhijeet Safai
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;
Many thanks to @ballardw , @Mazi , @data_null__ @yabwon @tom_grant , @PaigeMiller and all others for your response.
I was able to solve the issue but not easy way but by macro way. Somehow finally I found that the approach by macro will be better for me so I went ahead with that and the issue is solved. I would share the steps of what I did below.
And in this way the issue was solved. I would have liked to share the code for this but I will not be able to do it because I will need to type out the code again and that is very time consuming.
Many thanks to all those who responded. It was a fun exercise for me. I thought I will be able to avoid macro but later I found that macro is a better way to solve it. As I found this answer working for me, I will mark this as accepted answer.
Thank you.
- Dr. Abhijeet Safai
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.