topic Re: Count the number of non-missing values in a variable, by ID? in SAS Programming

Count the number of non-missing values in a variable, by ID?

UniversitySas — Mon, 24 Feb 2020 00:04:08 GMT

Suppose I have some data with three variables:

ID, Date, Var1.

I want to create a 'counter' variable for Var1, such that for each ID, every time Var1 is non-empty/non-missing, my var1_counter increases by 1.

The output would look like this:

data temp;
infile DATALINES dsd missover;
input ID DATE(month_num) Var1 Var1_Counter;
CARDS;
01, 1, ., .
01, 2, 3.5, 1
01, 3, ., 1
01, 4, 1.4, 2
01, 5, ., 2
02, 1, ., .
02, 2, 1.2, 1
02, 3, 1.4, 2
02, 4, 1.7, 3
02, 5, 5.1, 4
;
run;

Additionally, if I could just get the final count for each ID, (in my example final count for ID = 01 would be 2, for 02 it would be 4.

Thanks for the help!

Re: Count the number of non-missing values in a variable, by ID?

Reeza — Mon, 24 Feb 2020 00:58:35 GMT

I would suggest using PROC MEANS if the variable is numeric. If its character you'll need a different approach via PROC FREQ.

proc means data=have N NMISS;
class id;
var var1;
ods output summary=want;
run;

The output will be displayed and in the data set called WANT.

@UniversitySas wrote:

Suppose I have some data with three variables:

ID, Date, Var1.

I want to create a 'counter' variable for Var1, such that for each ID, every time Var1 is non-empty/non-missing, my var1_counter increases by 1.

The output would look like this:
data temp;
infile DATALINES dsd missover;
input ID DATE(month_num) Var1 Var1_Counter;
CARDS;
01, 1, ., .
01, 2, 3.5, 1
01, 3, ., 1
01, 4, 1.4, 2
01, 5, ., 2
02, 1, ., .
02, 2, 1.2, 1
02, 3, 1.4, 2
02, 4, 1.7, 3
02, 5, 5.1, 4
;
run;
Additionally, if I could just get the final count for each ID, (in my example final count for ID = 01 would be 2, for 02 it would be 4.

Thanks for the help!

Re: Count the number of non-missing values in a variable, by ID?

ChrisNZ — Mon, 24 Feb 2020 02:09:20 GMT

Like this?

data WANT;
  set HAVE;
  by ID;
  if first.ID then COUNTER=0;
  COUNTER+(VAR1 ne .);
run;

Re: Count the number of non-missing values in a variable, by ID?

ed_sas_member — Mon, 24 Feb 2020 09:26:33 GMT

Hi @UniversitySas

A complementary approach to the one proposed by @Reeza and @ChrisNZ , using PROC SQL:

proc sql;
	/* create table want as */
	select count(Var1) as Not_Missing,
		   nmiss(Var1) as Missing
	from temp
	group by ID;
run;

If you want to output the results of PROC MEANS:

proc means data=temp n nmiss;
	var Var1;
	class id;
	output out=want n= nmiss= / autoname;
run;

Best,

Re: Count the number of non-missing values in a variable, by ID?

nicobuettner — Mon, 24 Feb 2020 13:30:24 GMT

data temp2(drop=Counter);
  set temp;

  attrib Counter length=8;

  by ID;

  if first.ID then Counter = 0;

  if Var1 ne . then Counter+1; * retains Counter and adds 1;

  if Counter = 0 then Var1_Counter = .;
  else Var1_Counter = Counter;

  if last.ID then output; * use this if you only need totals;
run;

Re: Count the number of non-missing values in a variable, by ID?

Tom — Mon, 24 Feb 2020 13:36:11 GMT

Do you really want the counter to start with MISSING instead of ZERO?

data want;
  set temp;
  by id;
  if first.id then want=0;
  want + not missing(var1);
run;

             month_             Var1_
Obs    ID      num     Var1    Counter    want

  1     1       1        .        .         0
  2     1       2       3.5       1         1
  3     1       3        .        1         1
  4     1       4       1.4       2         2
  5     1       5        .        2         2
  6     2       1        .        .         0
  7     2       2       1.2       1         1
  8     2       3       1.4       2         2
  9     2       4       1.7       3         3
 10     2       5       5.1       4         4

Re: Count the number of non-missing values in a variable, by ID?

UniversitySas — Sun, 01 Mar 2020 01:21:47 GMT

Thanks, this is exactly what I was looking for. I had the logic down, but I was trying to condition "if first.ID" as "if first.var1".