topic Re: Missing values table in Statistical Procedures

Missing values table

Barney1998 — Thu, 30 Jun 2022 07:43:17 GMT

Dear all,

I have one big data set and i wanna to know which variables have at least one missing value .I know the two common ways (proc freq ,and where vr is missing).Ι wonder about if exist one order to print me all the variables names from my data set which have at least one missing value.

Thanks in advance.

Re: Missing values table

FreelanceReinh — Thu, 30 Jun 2022 08:23:08 GMT

Hello @Barney1998,

You can use the NLEVELS option of the PROC FREQ statement:

ods select none;
ods output nlevels=want(where=(nmisslevels));
proc freq data=have nlevels;
tables _all_;
run;
ods select all;

proc print data=want;
var tablevar;
run;

If your dataset is really big (e.g. >10 GB), you may want to temporarily restrict the number of observations (or variables) by means of dataset options such as OBS= (or KEEP=), e.g., have(obs=1000000), and see how the run time develops when you increase the limit.

Re: Missing values table

Barney1998 — Thu, 30 Jun 2022 08:28:52 GMT

with the nlevels from proc sql i have that type of problem. Existed columns which was empty but (i don't know why) the nmissing value from the output was equal to 0.

Thank you for your advice!

Re: Missing values table

Barney1998 — Thu, 30 Jun 2022 08:37:09 GMT

thanks so much for your solution! It's absolutely fine!
Have a nice day @FreelanceReinhard !
thanks a million!

Re: Missing values table

Ksharp — Thu, 30 Jun 2022 12:22:44 GMT

data have;
 set sashelp.heart;
run;



proc transpose data=have(obs=0) out=vname;
var _all_;
run;
proc sql noprint;
select cat('nmiss(',_name_,') as ',_name_) into :nmiss separated by ','
 from vname;

create table temp as
select &nmiss. from have;
quit;
proc transpose data=temp out=want(where=(col1>0));
var _all_;
run;