topic Re: how to take all missing variables list in dataset in SAS Programming

how to take all missing variables list in dataset

thanikondharish — Sat, 26 Jan 2019 16:12:29 GMT

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)

Re: how to take all missing variables list in dataset

PeterClemmensen — Sat, 26 Jan 2019 16:17:27 GMT

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

Re: how to take all missing variables list in dataset

Reeza — Sat, 26 Jan 2019 19:13:22 GMT

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

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)

Re: how to take all missing variables list in dataset

mkeintz — Sun, 27 Jan 2019 00:12:56 GMT

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.

Re: how to take all missing variables list in dataset

Ksharp — Sun, 27 Jan 2019 11:17:59 GMT

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 ;