topic Re: Amount of a field which is filled or empty in SAS Programming

Amount of a field which is filled or empty

Caro17 — Wed, 24 Feb 2021 06:26:32 GMT

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

Re: Amount of a field which is filled or empty

PeterClemmensen — Wed, 24 Feb 2021 06:29:34 GMT

Is the data numeric or character?

Re: Amount of a field which is filled or empty

Caro17 — Wed, 24 Feb 2021 06:32:07 GMT

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

Re: Amount of a field which is filled or empty

Amir — Wed, 24 Feb 2021 07:52:09 GMT

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.

Re: Amount of a field which is filled or empty

ballardw — Wed, 24 Feb 2021 08:07:48 GMT

@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.

Re: Amount of a field which is filled or empty

Caro17 — Wed, 24 Feb 2021 14:06:29 GMT

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?

Re: Amount of a field which is filled or empty

Caro17 — Wed, 24 Feb 2021 14:09:26 GMT

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

Re: Amount of a field which is filled or empty

ballardw — Wed, 24 Feb 2021 16:54:06 GMT

@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.

Re: Amount of a field which is filled or empty

ballardw — Wed, 24 Feb 2021 16:56:38 GMT

@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.