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

[shpetke]

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

[Reeza · Accepted solution]

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

---

[shpetke]

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. 

[shpetke]

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!

[shpetke]

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