topic Re: Counting missing and non missing at column level in SAS Procedures

Counting missing and non missing at column level

sasbeginner_us — Thu, 05 Feb 2015 22:59:50 GMT

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

Re: Counting missing and non missing at column level

Haikuo — Fri, 06 Feb 2015 00:26:17 GMT

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;

Re: Counting missing and non missing at column level

Reeza — Fri, 06 Feb 2015 01:01:15 GMT

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 observatins

Re: Counting missing and non missing at column level

Ksharp — Fri, 06 Feb 2015 08:28:52 GMT

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

Re: Counting missing and non missing at column level

KachiM — Fri, 06 Feb 2015 12:53:06 GMT

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;

keep col_name non_missing missing_cnt;

run;

Re: Counting missing and non missing at column level

sasbeginner_us — Mon, 09 Feb 2015 14:52:37 GMT

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