Help using Base SAS procedures

Metadata attributes and null records in a libary

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

Metadata attributes and null records in a libary

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


Accepted Solutions
Solution
‎06-12-2014 01:40 PM
SAS Super FREQ
Posts: 708

Re: Metadata attributes and null records in a libary

Posted in reply to rakeshvvv

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;

View solution in original post


All Replies
Super User
Posts: 19,774

Re: Metadata attributes and null records in a libary

Posted in reply to rakeshvvv

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

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 Smiley Happy

Good Luck!

Solution
‎06-12-2014 01:40 PM
SAS Super FREQ
Posts: 708

Re: Metadata attributes and null records in a libary

Posted in reply to rakeshvvv

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;
Frequent Contributor
Posts: 145

Re: Metadata attributes and null records in a libary

Posted in reply to Bruno_SAS

It has perfectly worked for my scenario.

Thanks

Frequent Contributor
Posts: 145

Re: Metadata attributes and null records in a libary

Posted in reply to rakeshvvv

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

SAS Super FREQ
Posts: 708

Re: Metadata attributes and null records in a libary

Posted in reply to rakeshvvv

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;

Super User
Posts: 10,023

Re: Metadata attributes and null records in a libary

Posted in reply to rakeshvvv

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

Super Contributor
Posts: 275

Re: Metadata attributes and null records in a libary

Posted in reply to rakeshvvv

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)

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 655 views
  • 3 likes
  • 5 in conversation