BookmarkSubscribeRSS Feed
sasbeginner_us
Calcite | Level 5

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

 


5 REPLIES 5
Haikuo
Onyx | Level 15

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
Calcite | Level 5

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

Reeza
Super User

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
Super User
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
Rhodochrosite | Level 12

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;

    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
    • 5 replies
    • 1022 views
    • 0 likes
    • 5 in conversation