topic Re: Fill missing values in all variables of a dataset in SAS Programming

Fill missing values in all variables of a dataset

simmwa — Tue, 23 Mar 2021 22:58:21 GMT

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;

Re: Fill missing values in all variables of a dataset

qoit — Wed, 24 Mar 2021 00:01:10 GMT

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

Re: Fill missing values in all variables of a dataset

Patrick — Wed, 24 Mar 2021 00:52:26 GMT

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.

Re: Fill missing values in all variables of a dataset

simmwa — Wed, 24 Mar 2021 03:35:01 GMT

Thanks @qoit it works perfectly !

Re: Fill missing values in all variables of a dataset

dscastelino — Wed, 12 May 2021 02:57:50 GMT

Is there a way to include a macro for multiple HAVE datasets.