Amount of a field which is filled or empty

[Caro17]

Hey,

I just want to have a simple thing, but I can't find a solution.

I want to count a field how often it is filled or empty.

How can I do this?

Thanx a lot for you help.

Best regards

Caro

[PeterClemmensen]

Is the data numeric or character?

[Caro17]

Can be both. It depends on the field. Therefore I want to have a solution for both cases, please. Thank you!

[Amir]

Hi @Caro17,

Have you tried something like the below. If you need something different then please provide some examples of input data and what you want to see output.

/* create data with missing values */
data have;
   infile datalines truncover dsd;

   input
      char_var : $10.
      num_var  :   8.
   ;

   datalines;
abc,123
def,
,456
,
,789
;


/* initialise counts */
%let char_var_missing_count = 0;
%let num_var_missing_count  = 0;


/* count missing values */
proc sql noprint;
   select
       sum(missing(char_var))
      ,sum(missing(num_var))
   into
       :char_var_missing_count
      ,:num_var_missing_count
   from
      have
   ;
quit;


/* display values in log */
%put &=char_var_missing_count;
%put &=num_var_missing_count;

Kind regards,

Amir.

[ballardw]

---

@Caro17 wrote:

Hey,

I just want to have a simple thing, but I can't find a solution.

I want to count a field how often it is filled or empty.

How can I do this?

Thanx a lot for you help.

Best regards

Caro

---

One way:

data have;
   input x y $;
datalines;
1 a
2 .
. b
4 c
. .
5 f
6 .
;

proc format;
value nummiss
.= 'Missing'
other = 'Not missing'
;
value $charmiss
' '='Missing'
other= 'Not missing'
;
run;

proc freq data=have;
  tables _all_/missing;
  format _numeric_ nummiss. _character_ $charmiss.;
run;

The custom formats are designed so that filled (not missing) and not filled (missing) are reported.

Proc freq uses a small example data that was build to include a few missing values to demonstrate. The _all_ on the tables statement says to analyze all variables. The option MISSING on the tables statement says to include the missing values in the counting.

The format using special variable list names _numeric_ and _character_ , meaning all numeric and all character variables and assigns the custom format.

[Caro17]

This one works. Thank you very much.

How can I do it if the variable is a date or a checkbox (yes/no)? What have to be changed?

[ballardw]

---

@Caro17 wrote:
This one works. Thank you very much.

How can I do it if the variable is a date or a checkbox (yes/no)? What have to be changed?

---

If the concern is missing or not, then nothing would need to be changed. SAS datasets only have two data types: numeric and character. Data imported to SAS will become one of those types.

If your mean something else, provide actual example data and what you expect as a result for that example.

[Caro17]

Is it possible to do it for more variables in one table?

[ballardw]

---

@Caro17 wrote:
Is it possible to do it for more variables in one table?

---

You would have to provide some example data and what you expect.

It may be that what you want here would come by creating an output data set from proc freq. That could be done by adding

ods onewayfreqs= freqdatasetname;

Then print that data set, or manipulate as needed.