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)
And what do you want to do with that variable? Put it in a macro variable or?
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)
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.
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 ;
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.