topic Re: How would I Count Missing Values for all Columns in a Table BY another Column in the Table in SAS Enterprise Guide

How would I Count Missing Values for all Columns in a Table BY another Column in the Table

shpetke — Mon, 17 Jul 2017 21:46:04 GMT

I am a very new SAS EG version 6.1 user with limited training. I searched and found a solution that kind of meets my needs, but not 100% which is located here: http://blogs.sas.com/content/iml/2011/09/19/count-the-number-of-missing-values-for-each-variable.html A lot of it is over my head, but a section seems to work (see code below) with one exception.

PROC FORMAT;
	value $missfmt ' '='Missing' other='Not Missing';
	value  missfmt  . ='Missing' other='Not Missing';
RUN;

PROC FREQ DATA=CCW.INTGRD_INPA;
FORMAT _CHAR_ $missfmt.;
TABLES _CHAR_ / missing missprint nocum nopercent;
FORMAT _NUMERIC_ missfmt.;
TABLES _NUMERIC_ / missing missprint nocum nopercent;	
;
RUN;

My results for one column looks like below, but I need to see the columns BY another field, so CLIENT_ID by SRC_SYS for Missing/Not Missing and export the results to Excel. How would I accomplish this? Thanks! Sue

CLIENT_ID CLIENT_ID Frequency

Missing	249897
Not Missing	2928702

Re: How would I Count Missing Values for all Columns in a Table BY another Column in the Table

Reeza — Mon, 17 Jul 2017 21:57:17 GMT

PROC FREQ DATA=CCW.INTGRD_INPA;
BY CLIENT_ID;

FORMAT _CHAR_ $missfmt.;
TABLES _CHAR_ / missing missprint nocum nopercent;
FORMAT _NUMERIC_ missfmt.;
TABLES _NUMERIC_ / missing missprint nocum nopercent;	
;
RUN;

Have you tried adding a BY statement?

This isnt' a macro but you can also try modifying my code here to see if helps you get a single table.

https://gist.github.com/statgeek/e0903d269d4a71316a4e

@shpetke wrote:

I am a very new SAS EG version 6.1 user with limited training. I searched and found a solution that kind of meets my needs, but not 100% which is located here: http://blogs.sas.com/content/iml/2011/09/19/count-the-number-of-missing-values-for-each-variable.html A lot of it is over my head, but a section seems to work (see code below) with one exception.
PROC FORMAT;
	value $missfmt ' '='Missing' other='Not Missing';
	value  missfmt  . ='Missing' other='Not Missing';
RUN;

PROC FREQ DATA=CCW.INTGRD_INPA;
FORMAT _CHAR_ $missfmt.;
TABLES _CHAR_ / missing missprint nocum nopercent;
FORMAT _NUMERIC_ missfmt.;
TABLES _NUMERIC_ / missing missprint nocum nopercent;	
;
RUN;
My results for one column looks like below, but I need to see the columns BY another field, so CLIENT_ID by SRC_SYS for Missing/Not Missing and export the results to Excel. How would I accomplish this? Thanks! Sue

CLIENT_ID CLIENT_ID Frequency

Missing 249897

Not Missing 2928702

Re: How would I Count Missing Values for all Columns in a Table BY another Column in the Table

shpetke — Tue, 18 Jul 2017 12:13:44 GMT

I did try the BY statement for CHNL_SRC_CD but it is not the right result. None of the CHNL_SRC_CD's are missing. The result came out like below. I want to see the result for each column in the table by CHNL_SRC_CD.

The FREQ Procedure

CHNL_SRC_CD=Not Missing

ACCT_SUBTY_CD ACCT_SUBTY_CD Frequency

Missing	1727253
Not Missing	1452962

ACCT_SUBTY_2_CD ACCT_SUBTY_2_CD Frequency

Missing	250696
Not Missing	2929519

ACCT_TY_CD ACCT_TY_CD Frequency

Missing	1727253
Not Missing	1452962

I'd like to see something like this where SourceSysn is the value in field CHNL_SRC_CD.

	Missing	Not Missing
ACCT_SUBTY_CD
SourceSys1
SourceSys2
SourceSys3
SourceSys4
ACCT_SUBTY_2_CD
SourceSys1
SourceSys2
SourceSys3
SourceSys4
ACCT_TY_CD
SourceSys1
SourceSys2
SourceSys3
SourceSys4

Re: How would I Count Missing Values for all Columns in a Table BY another Column in the Table

shpetke — Tue, 18 Jul 2017 12:31:33 GMT

Oh progress! I added the statement: FORMAT CHNL_SRC_CD $CHARw.;

Now I get the data by source code! I realize what I was doing with the first format statement. I'll look at the link to the other reference Reeza provided to see if I can get a better table result.

Re: How would I Count Missing Values for all Columns in a Table BY another Column in the Table

shpetke — Tue, 18 Jul 2017 14:30:35 GMT

I was able to modify the code in the link Reeza provided, so I have what I am looking for. I can't say that I understand all the code, but it's working. Thank you!