@Antoni wrote:
data wanted;
set sklejone( keep=act_age -- default_cus12);
array t act_age -- default_cus12;
do over t;
if t= 'M' then t=' ';
end;
run;
thats this one, i checked and had previously included character variables, i have included the right range of ony numerical variables and it did nothing, no error, no expected output
You didn't answer the question and you don't seem to understand what we've been saying about missing values.
You need to read the page below about SAS special missing values.
You can have missing values as just missing, or you can code them to identify different types of missing values. For example, in survey data, I will often code N/A questions as .N and skipped questions/no answer as .M so they can be differentiated in the results.
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lrcon&docsetTarget=p1xr9f...
So modifying my code according to the comment from @Astounding earlier in the thread, give the following macro definition. This should work now, I tested it.
%macro drop_missing_pct(input_dsn = , output_dsn=, pct = , id_vars=);
*create format for missing;
proc format;
value $ missfmt ' '="Missing" other="Not Missing";
value nmissfmt ._ - .Z ="Missing" other="Not Missing";
run;
*Proc freq to count missing/non missing;
ods select none;
*turns off the output so the results do not get too messy;
ods table onewayfreqs=temp;
proc freq data=&INPUT_DSN. (drop = &ID_Vars);
table _all_ / missing;
format _numeric_ nmissfmt. _character_ $missfmt.;
run;
ods select all;
*Format output;
data long;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);
Variable_Value=strip(trim(vvaluex(variable)));
presentation=catt(frequency, " (", trim(put(percent/100, percent7.1)), ")");
keep variable variable_value frequency percent cum: presentation;
label variable='Variable' variable_value='Variable Value';
run;
*not required for display purposes;
proc sort data=long;
by variable;
run;
*select variables more than x% missing;
proc sql noprint;
select variable into :drop_var_list separated by " "
from long where variable_value = 'Missing' and percent > &pct;
quit;
*Drop variables;
data &output_dsn;
set &input_dsn;
drop &drop_var_list;
run;
*clean up;
*uncomment after testing;
/* proc sql; */
/* drop table long; */
/* drop table temp; */
/* quit; */
%mend;
Example showing it works, with special missing values:
*create test data set with many missing values;
data class;
set sashelp.class;
if age=13 then
call missing(height, weight, sex);
if name in ('Jane', 'Carol', 'Henry', 'Alfred') then
do;
age=.A;
sex=.M;
end;
if name in ('Judy', 'Mary') then
weight=.W;
run;
%drop_missing_pct(input_dsn=class, output_dsn=want, pct=20, id_vars=Name);
*check output;
proc contents data=want;
run;
title 'Missing Report for data set';
proc print data=long;
run;
title 'Data set with columns missing more than 20% dropped';
proc print data=want (obs=5);
run;
