Counting missing and non missing at column level

[sasbeginner_us]

Hi,

I am a SAS beginner level developer..I have a sas dataset as below, and have close to 100 columns like this including char and numeric types. Need to calculate number of missing and non-missing for each of this column and create a sas dataset as in expected output. Please assist me if we can do this using proc freq...

obs   open_date        desc_code   open_amt

1         02-02-2011       AAC               2900

2         04-02-2000       TEST1           09.00

3          03-09-2014       .                     1000

4          05-03-2001      .                       .

5          .                       ESS                 300

Expected output :

column_name    non-missing     missing_cnt

open_date          4                         1

desc_code          3                         2

open_amt           4                         1

thanks in advance...

[Haikuo]

If you have 9.4, then:

data  have;

     input open_date:mmddyy10.    desc_code :$5. open_amt;

     cards;

       02-02-2011       AAC               2900

       04-02-2000       TEST1           09.00

        03-09-2014       .                     1000

        05-03-2001      .                       .

        .                       ESS                 300

           ;

run;

proc sql NOPRINT;

     create table h1 as

           select distinct name from dictionary.columns where LIBNAME='WORK' AND MEMNAME='HAVE';

     SELECT NOBS INTO :NOBS FROM DICTIONARY.TABLES WHERE LIBNAME='WORK' AND MEMNAME='HAVE';

QUIT;

DATA WANT;

     SET H1;

     CALL SYMPUTX('NAME', NAME);

     _RC=DOSUBL('PROC SQL NOPRINT; SELECT N(&NAME), &NOBS-N(&NAME) INTO :_NON_MISSING, :_MISSING FROM HAVE;QUIT;');

     CT_MISSING=SYMGET('_MISSING');

     CT_NON_MISSING=SYMGET('_NON_MISSING');

     DROP _RC;

RUN;

[sasbeginner_us]

Thank you for your quick reply. Unfortunately, we dont have 9.4, use only 9.3

[Reeza]

I wrote a macro for that a while back. There should be an easier way to do this in SAS.

This is an example of how to call the macro (also included at the end of the code)

data class;

set sashelp.class;

if age=14 then call missing(height, weight, sex);

if name='Alfred' then call missing(sex, age, height);

label age="Fancy Age Label";

run;

%sum_missing(work, class, class_missing);

proc print data=class_missing;

run;

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

[Ksharp]

data class;
 set sashelp.class;
 if ranuni(0) lt 0.3 then call missing(name);
 if ranuni(0) lt 0.6 then call missing(age);
run;


data _null_;
 set sashelp.vcolumn(where=(libname='WORK' and memname='CLASS') keep=libname memname name) end=last;
 if _n_ eq 1 then call execute('proc sql;create table want as ');
 call execute('select "'||strip(name)||'" as vname length=40,n('||strip(name)||') as Non_Missing,nmiss('||strip(name)||') as Missing from class ');
 if not last then call execute('union');
  else call execute(';quit;');
run;

Xia Keshan

[KachiM]

Yet another solution using Arrays.

data have;

informat open_date mmddyy10.;

input open_date desc_code $   open_amt;

datalines;

02-02-2011       AAC          2900

04-02-2000       TEST1        09.00

03-09-2014       .            1000

05-03-2001       .             .

     .           ESS          300

;

run;

data want;

   set have end = eof;

   array n

_numeric_;

   array c

_character_;

   array nn_nomiss[2] _temporary_;

   array nn_miss[2] _temporary_;

   array cn_nomiss[1] _temporary_;

   array cn_miss[1] _temporary_;

   length col_name $10;

   do i = 1 to dim(n);

      if not missing(n) then nn_nomiss + 1;

      if     missing(n) then nn_miss + 1;

   end;

      do i = 1 to dim(c);

      if not missing(c) then cn_nomiss + 1;

      if     missing(c) then cn_miss + 1;

   end;

   if eof then do;

      do i = 1 to dim(n);

         col_name = vname(n);

         non_missing = nn_nomiss;

         missing_cnt = nn_miss;

         output;

      end;

      do i = 1 to dim(c);

         col_name = vname(c);

         non_missing = cn_nomiss;

         missing_cnt = cn_miss;

         output;

      end;   

   end;

keep col_name non_missing missing_cnt;

run;