BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shpetke
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

4 REPLIES 4
Reeza
Super User
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
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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  

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 4 replies
  • 5951 views
  • 2 likes
  • 2 in conversation