Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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
Missing249897
Not Missing2928702

 


Accepted Solutions
Solution
a week ago
Grand Advisor
Posts: 17,462

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

[ Edited ]
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

 


 

View solution in original post


All Replies
Solution
a week ago
Grand Advisor
Posts: 17,462

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

[ Edited ]
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

 


 

New Contributor
Posts: 4

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

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. 

New Contributor
Posts: 4

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

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!

New Contributor
Posts: 4

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

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

 

ACCT_SUBTY_CD ACCT_SUBTY_CD Frequency
Missing1727253
Not Missing1452962

 

ACCT_SUBTY_2_CD ACCT_SUBTY_2_CD Frequency
Missing250696
Not Missing2929519

 

ACCT_TY_CD ACCT_TY_CD Frequency
Missing1727253
Not Missing1452962

 

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

 MissingNot Missing
ACCT_SUBTY_CD  
SourceSys1  
SourceSys2  
SourceSys3  
SourceSys4  
ACCT_SUBTY_2_CD  
SourceSys1  
SourceSys2  
SourceSys3  
SourceSys4  
ACCT_TY_CD  
SourceSys1  
SourceSys2  
SourceSys3  
SourceSys4  
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 154 views
  • 0 likes
  • 2 in conversation