I have a library X with N number of datasets……I want to write a macro in such a way that it will check all the datasets and outputs metadata of all datasets with additional column of COMMENT saying variable is Null for all records or not.
EX: We have library with two dataset( but we have N number of datasets) AE and CE with below variable. I would like to output all the variables in the datasets and display if that variable is null for all records or not…..
SASNAME COMMENTS
CESPID Null
CETRTNY Not null
CEENDTC Not null
CEENDY Null
SASNAME COMMENTS
AEBDSYCD Null
AEHLGT Null
AEHLT Not null
AEHLTCD Not null
AELLT Not null
AELLTCD Not null
AEPTCD Not null
AESOC Null
AESOCCD Null
You can take advantage of the NLEVELS option in the Proc FREQ procedure. See sample code below:
Now you need to do this for every table in your library, a good starting point is the blog entry Implement BY processing for your entire SAS program - The SAS Dummy by
To get a list of tables in a library you can use the dictionary tables like this:
You can use the sashelp.vcolumn or dictionary.columns table to list all the variables in a table.
To calculate the number of missing you have to analyze each variable.
I have a macro that does the number missing here:
What you could do is query the sashelp.vtable for all tables that meet your naming convention.
Then in a second data step with a call execute to run the missing macro and finally append all results.
Or rewrite it to handle it all in one macro
Good Luck!
You can take advantage of the NLEVELS option in the Proc FREQ procedure. See sample code below:
Now you need to do this for every table in your library, a good starting point is the blog entry Implement BY processing for your entire SAS program - The SAS Dummy by
To get a list of tables in a library you can use the dictionary tables like this:
It has perfectly worked for my scenario.
Thanks
Hi Bruno,
ods output NLevels=nlevels;
proc freq data=newClass nlevels;
tables _all_ / noprint;
run;
is there is any option to output only to the dataset with out printing the listing......As I am using it in a macro for a library of around 40 datasets.....it is creating lot of lst file......if i could output only to dataset with out printing, it would enhance my code...............
add
ODS SELECT NONE;
like
* count values for each variable ;
ods select none;
ods output NLevels=nlevels;
proc freq data=newClass nlevels;
tables _all_ / noprint;
run;
ods select all;
Post here .For someone who need help.
data newClass; set sashelp.class; if ranuni(0) < 0.2 then do; call missing(name); end; if ranuni(0) < 0.5 then do; call missing(age); end; height = .; empty = .; run; data oldClass; set sashelp.class; run; proc sql; create table x as select a.memname, cat('nmiss(',strip(name),')=',nlobs,' as ',name) as stat from (select memname,nlobs from dictionary.tables where libname='WORK') as a , (select memname,name from dictionary.columns where libname='WORK') as b where a.memname=b.memname ; quit; data _null_; set x ; by memname; if first.memname then call execute('proc sql; create table temp as select "'||memname||'" as memname length=40 ,'); call execute(stat); if not last.memname then call execute(','); else call execute(' from '||memname||' ;quit; proc transpose data=temp out=_'||strip(_n_)||';by memname;var _numeric_;run;'); run; data want; set work._: ; length flag $ 20; flag=ifc(col1=0,'not null','null'); drop col1; run;
Xia Keshan
Message was edited by: xia keshan
Use Macro program:
%macro vars_null(lib,dsn);
data want;
length varname $20;
lenght flag $20;
run;
data _null_;
set sashelp.vcolumn(where=(libname=upcase("&lib") and memname=upcase("&dsn")));
call symputx('var',name);
call execute
(
'data temp;
set &&lib..&dsn(keep=&var) end=last;
length varname $20;
length flag $20;
if missing(&var) then count+1;
if last then do;
varname=vname(&var);
if count=_n_ then flag="Null";
else flag="Not Null";
output;
end;
keep varname flag;
run;
data want;
set want temp;
run;'
);
run;
%mend;
%vars_null(work, newclass)
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.