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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.