BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rakeshvvv
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

7 REPLIES 7
Reeza
Super User

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!

BrunoMueller
SAS Super FREQ

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;
rakeshvvv
Quartz | Level 8

It has perfectly worked for my scenario.

Thanks

rakeshvvv
Quartz | Level 8

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

BrunoMueller
SAS Super FREQ

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;

Ksharp
Super User

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

slchen
Lapis Lazuli | Level 10

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)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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