how to take all missing variables list in dataset

[thanikondharish]

Obs	Name	Sex	Age	Height	Weight
1	Alfred	M	14	69.0	.
2	Alice	F	13	56.5	.
3	Barbara	F	13	65.3	.
4	Carol	F	14	62.8	.
5	Henry	M	14	63.5	.
6	James	M	12	57.3	.
7	Jane	F	12	59.8	.
8	Janet	F	15	62.5	.
9	Jeffrey	M	13	62.5	.
10	John	M	12	59.0	.
11	Joyce	F	11	.	.
12	Judy	F	14	64.3	.
13	Louise	F	12	56.3	.
14	Mary	F	15	66.5	.
15	Philip	M	16	72.0	.
16	Robert	M	12	64.8	.
17	Ronald	M	15	67.0	.
18	Thomas	M	11	.	.
19	William	M	15	66.5	.

i want to make list out the missing variables list like  if you take weight variable all row are missing so that type of missing varibles i want to make out (dont height sex varibles)

[PeterClemmensen]

And what do you want to do with that variable? Put it in a macro variable or?

[Reeza]

https://communities.sas.com/t5/forums/searchpage/tab/message?q=drop+variable+with+all+missing&collap...

This question gets asked pretty regularly. 

---

@thanikondharish wrote:

Obs	Name	Sex	Age	Height	Weight
1	Alfred	M	14	69.0	.
2	Alice	F	13	56.5	.
3	Barbara	F	13	65.3	.
4	Carol	F	14	62.8	.
5	Henry	M	14	63.5	.
6	James	M	12	57.3	.
7	Jane	F	12	59.8	.
8	Janet	F	15	62.5	.
9	Jeffrey	M	13	62.5	.
10	John	M	12	59.0	.
11	Joyce	F	11	.	.
12	Judy	F	14	64.3	.
13	Louise	F	12	56.3	.
14	Mary	F	15	66.5	.
15	Philip	M	16	72.0	.
16	Robert	M	12	64.8	.
17	Ronald	M	15	67.0	.
18	Thomas	M	11	.	.
19	William	M	15	66.5	.

i want to make list out the missing variables list like  if you take weight variable all row are missing so that type of missing varibles i want to make out (dont height sex varibles)

---

[mkeintz]

This program takes two preliminary steps instead of one, but it's much faster for large datasets.

It assumes that you can discover most of the variables that have non-missing value will be discovered in the first observations.  So find out what they are, and then run another discovery step but don't bother with the ones already discovered.

data have;
  set sashelp.class;
  call missing (weight);
  if _n_ in (11,18) then height=.;
run;


data _null_;
  set have (obs=1) end=end_of_have1;
  array _nums {*} _numeric_;
  array _chrs {*} _character_;

  length keep_list1 $300;
  if dim(_nums)>0 then do i=1 to dim(_nums);
    if not missing(_nums{i}) then keep_list1=catx(' ',keep_list1,vname(_nums{i}));
  end;
  if dim(_chrs)>0 then do i=1 to dim(_chrs);
    if not missing(_chrs{i}) then keep_list1=catx(' ',keep_list1,vname(_chrs{i}));
  end;
  if end_of_have1 then call symput('keep_list1',cats(keep_list1));
run;

data _null_;
  set have (drop=&keep_list1) end=end_of_have2;
  array _nums {*} _numeric_;
  array _chrs {*} _character_;

  length keep_list2 $300;
  if dim(_nums)>0 then do i=1 to dim(_nums);
    if not missing(_nums{i}) then keep_list2=catx(' ',keep_list2,vname(_nums{i}));
  end;
  if dim(_chrs)>0 then do i=1 to dim(_chrs);
    if not missing(_chrs{i}) then keep_list2=catx(' ',keep_list2,vname(_chrs{i}));
  end;
  if end_of_have2 then call symput('keep_list2',cats(keep_list2));
run;


%put _user_;
data want;
  set have (keep=&keep_list1 &keep_list2);
run;

The first step use the OBS=1 parameter, but you could just as easily used OBS=100 as your initial sample.

[Ksharp]

ods select none;
ods output nlevels=want;
proc freq data=have nlevels;
table _all_;
run;
ods select all;
proc sql noprint;
select TableVar into : missing_variables separated by ' '
from want
where NNonMissLevels=0;
quit;
%put Missing variables are: &missing_variables ;