I need some code that will loop through all variables in a dataset, and check each variable for missing values.
If any missing are found then fill with # characters (or value 9) based on the length of that particular variable.
Here is my poor attempt:
data want;
dsid=open("have", "i");
n_vars=attrn(dsid, "nvars");
do i=1 to n_vars;
Name=varname(dsid, i);
Type=vartype(dsid, i);
set have;
if missing(Name) then do;
if Type = 'C' then
Name = repeat('#', length(Name));
else
Name = repeat(9, length(Name));
end;
end;
rc=close(dsid);
run;
In case you just want to do this for reporting purposes then consider to not change the data but to use formats and/or options missing='#'; instead.
At its very minimum, below should do the job. Also REPEAT function returns a character value so I'd suggest keeping missing numeric values as "99" or "999", below:
data have;
set sashelp.class;
if name = 'James' then call missing(sex,weight);
run;
%macro test;
%local i;
proc sql;
select name,type,length
into: name1 - :name&sysmaxlong,
:type1 - :type&sysmaxlong,
:len1 - :len&sysmaxlong
from dictionary.columns
where libname = 'WORK'
and memname = 'HAVE';
%let cnt = &sqlobs;
quit;
data want;
set have;
%do i = 1 %to &cnt.;
%if &&type&i. = char %then
%do;
if missing(&&name&i.) then
&&name&i. = repeat('#', &&len&i.);
%end;
%else
%do;
if missing(&&name&i.) then
&&name&i. = 99;
/*&&name&i. = input(strip(repeat(9, &&len&i.)),8.);*/
%end;
%end;
run;
%mend;
%test
Thanks @qoit it works perfectly !
Is there a way to include a macro for multiple HAVE datasets.
In case you just want to do this for reporting purposes then consider to not change the data but to use formats and/or options missing='#'; instead.
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.