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
Missing | 249897 |
Not Missing | 2928702 |
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
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
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.
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!
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 |
Missing | 1727253 |
Not Missing | 1452962 |
Missing | 250696 |
Not Missing | 2929519 |
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.