topic Re: How to get number of non-missing without running the entire frequency in SAS Procedures

How to get number of non-missing without running the entire frequency

SAShole — Wed, 02 Jul 2014 14:58:46 GMT

Hi SAS friends,

is there a way to run a frequency on a variable without displaying every level for that variable? For example i have a variable called Phone for which i have 1M observations. I don't want to know the number of occurrences of phone i just want to know if its missing. If i run the normal proc freq with the missing option it takes 20+minutes to get results.

I'm running SAS 9.4 with STAT.

Thank you,

data BigDS;

do i = 1 to 1000000;

PhoneNums=INT((RanUni(07022014)*10000000000));

MissRate=RanUni(1234);

if MissRate>=.8 then call missing(PhoneNums);

keep PhoneNums;

output;

end;

run;

proc freq data = BigDS;

table PhoneNums/missing;

run;

Re: How to get number of non-missing without running the entire frequency

RW9 — Wed, 02 Jul 2014 15:01:15 GMT

You could do a proc sort with nodupkey. Or alternatively use:
proc sql;

create table WANT as

select count(1)

from HAVE

where PHONENUMS is null;

quit;

As for freq, I don't use it so don't know.

Re: How to get number of non-missing without running the entire frequency

slchen — Wed, 02 Jul 2014 15:08:24 GMT

proc sql;
select nmiss(phonenums) from bigds;
quit;

Re: How to get number of non-missing without running the entire frequency

Astounding — Wed, 02 Jul 2014 15:16:10 GMT

Outside of the SQL world, there are two standard ways to accomplish this. First, you could switch to PROC MEANS:

proc means data=have n nmiss;

var phonenums;

run;

The advantage here is that you can easily add many variables to the VAR statement.

To use PROC FREQ, apply a format that groups all nonmissing values:

proc format;

value present low-high='Present' other='Missing';

run;

Then use the format:

proc freq data=have;

tables phonenum / missing;

format phonenum present.;

run;

Only testing will tell you which is fastest.

Re: How to get number of non-missing without running the entire frequency

SAShole — Wed, 02 Jul 2014 15:38:13 GMT

Astounding is there a low-high equivalent for character variables?

Re: How to get number of non-missing without running the entire frequency

Astounding — Wed, 02 Jul 2014 15:47:19 GMT

For character variables, the range low-high does not help because the hex code for a blank falls in the middle of the range of possible values. Instead, this format would do the trick:

proc format;

value $present ' '='Missing' other='Present';

run;

Re: How to get number of non-missing without running the entire frequency

ChrisHemedinger — Wed, 02 Jul 2014 16:26:45 GMT

Your original program might also pay a penalty for creating large ODS output, depending on your settings. If running in SAS Enterprise Guide or in SAS Display Manager with default settings, you're creating a large HTML or SAS Report output. The creation/transfer of that file would slow down the overall process.

has good suggestions for how to reduce the "counting" part of the process, making it much more efficient. But your original approach would run pretty fast if you output just a data set, and not any report.

proc freq data = BigDS noprint;

table PhoneNums/missing out=miss_count;

run;

Or optionally, to declutter the output:

proc freq data = BigDS noprint;

table PhoneNums/missing out=miss_count(where=(PhoneNums=.));

run;

Chris

Re: How to get number of non-missing without running the entire frequency

SAShole — Wed, 02 Jul 2014 16:44:13 GMT

Thank you everyone for your input. i like asking these questions because i always learn multiple ways to accomplish something.

Re: How to get number of non-missing without running the entire frequency

Tom — Wed, 02 Jul 2014 20:06:51 GMT

If you just want to know where there are ANY missing values then use the NLEVELS output from proc FREQ.

proc freq data = BigDS nlevels ;

table PhoneNums / noprint;

run;

Re: How to get number of non-missing without running the entire frequency

SAShole — Wed, 02 Jul 2014 20:50:56 GMT

Tom that is awesome! can you route the results of a Nlevels table to a dataset?

Re: How to get number of non-missing without running the entire frequency

Tom — Wed, 02 Jul 2014 22:02:36 GMT

ODS OUTPUT.

But watch because if there are no missing values then the variable NMissLevels will not even be on the output dataset.

ods output nlevels=nlevels;

ods exclude nlevels;

proc freq data = BigDS nlevels ;

table PhoneNums / noprint;

run;

1722 data _null_; set nlevels; put (_all_) (=/); run;

TableVar=PhoneNums

NLevels=799696

NMissLevels=1

NNonMissLevels=799695

Re: How to get number of non-missing without running the entire frequency

sarak — Mon, 24 Aug 2015 20:42:56 GMT

Tom, how does this produce a table? I'm also trying to save the value from running nlevels so I can merge it in with other data. I just need the one count variable produced with nlevels, but when I try to save the output it saves the underlying individual level data, not the nlevels value.

Re: How to get number of non-missing without running the entire frequency

Tom — Tue, 25 Aug 2015 00:14:02 GMT

The ODS OUTPUT statement tells it to save the output that the NLEVELS option produces as a dataset.

The ODS EXCLUDE statement tells it to not produce any printed output for the NLEVELS option.

The NOPRINT option on the TABLES statement tells it not to produce any printed output for the variable frequencies.

So all that it produces is the dataset with the number of levels (and number of missing levels if any).

Re: How to get number of non-missing without running the entire frequency

MikeZdeb — Tue, 25 Aug 2015 02:32:34 GMT

Hi ... if you have a number of variables (NUM, CHAR, or both types), you can try the method in this paper (it uses NLEVELS)...

An Easy Route to a Missing Data Report with ODS+PROC FREQ+A Data Step

http://www.lexjansen.com/nesug/nesug11/ds/ds12.pdf

Using your data step to create 3 numeric variables ...

data x;

do i = 1 to 1000000;

Phone1=INT((RanUni(07022014)*10000000000));

phone2 = phone1;

phone3 = phone2;

if RanUni(1234)>=.8 then call missing(Phone1);

if RanUni(1234)>=.9 then call missing(Phone2);

if RanUni(1234)>=.4 then call missing(Phone3);

output;

end;

keep Phone: ;

run;

CPU time was < 1 second to produce a report ...

Obs var miss p_miss ok p_ok

1 Phone1 199,849 20.0 800,151 80.0

2 phone2 99,747 10.0 900,253 90.0

3 phone3 600,976 60.1 399,024 39.9

You can also use PROC MI to look for patterns in missing numeric data if you have two or more numeric variables (takes longer than the NLEVELS approach) ...

proc mi data=x;

ods select misspattern;

run;

Re: How to get number of non-missing without running the entire frequency

advoss — Tue, 15 Sep 2015 17:11:07 GMT

Here's another solution.

proc sql noprint;

create table nmiss_or_not as

select sum(missing(j)) as NMISS,

count(*) as TOTAL_ROWS

from bigds;

quit;

Re: How to get number of non-missing without running the entire frequency

bbenbaruch — Thu, 17 Sep 2015 19:16:41 GMT

I have been using the following in my autoexec file for the last 12 years or so:

PROC FORMAT /*LIB=BBBANAL.SEGFORMATS*/ ;
VALUE MISSING .="Missing" Other="Not Missing" ;
VALUE $MISSING ' '="Missing" Other="Not Missing" ;

RUN ;