topic Re: Metadata attributes and null records in a libary in SAS Procedures

Metadata attributes and null records in a libary

rakeshvvv — Thu, 12 Jun 2014 15:37:12 GMT

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

Re: Metadata attributes and null records in a libary

Reeza — Thu, 12 Jun 2014 17:37:48 GMT

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:

SAS - Missing Macro - For a dataset, variable level report the number of missing and non-missing observatins

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!

Re: Metadata attributes and null records in a libary

BrunoMueller — Thu, 12 Jun 2014 17:40:04 GMT

You can take advantage of the NLEVELS option in the Proc FREQ procedure. See sample code below:

* create some test data ;
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; 

* count values for each variable ;
ods output NLevels=nlevels;
proc freq data=newClass nlevels; 
  tables _all_ / noprint; 
run; 

* check for empty variables ;
data colInfo;
  set nlevels;
  vComment = (nLevels = 1 and nMissLevels = 1 and nNonMissLevels = 0);
run; 

* format to print ;
proc format; 
  value nullComment
    1 = "null"
    0 = "not null"
  ;
run; 

proc print data=colInfo;
  format vComment nullComment.; 
run; 

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:

proc sql; 
  create table myTables as
  select
    catx(".", libname, memname) as tableName length=41
  from  
    dictionary.tables
  where
    libname = 'WORK'
  ;
quit;

Re: Metadata attributes and null records in a libary

rakeshvvv — Thu, 12 Jun 2014 20:22:46 GMT

It has perfectly worked for my scenario.

Thanks

Re: Metadata attributes and null records in a libary

rakeshvvv — Fri, 13 Jun 2014 13:03:17 GMT

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...............

Re: Metadata attributes and null records in a libary

Ksharp — Fri, 13 Jun 2014 13:47:55 GMT

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

Re: Metadata attributes and null records in a libary

slchen — Fri, 13 Jun 2014 14:14:57 GMT

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)

Re: Metadata attributes and null records in a libary

BrunoMueller — Fri, 13 Jun 2014 21:52:31 GMT

add

ODS SELECT NONE;

* count values for each variable ;

ods select none;

ods output NLevels=nlevels;

proc freq data=newClass nlevels;

tables _all_ / noprint;

run;

ods select all;