proc sql count for group and where clause without losing observations

[raja777pharma]

Hi Team,

I would like to count the records based on group vars and another var VALUE is not missing.

My data :

data my_data;
	input subject $ test $ visit $ seq value;
	datalines;
001 PULSE VISIT1 01 70
001 PULSE VISIT1 02 80
001 DBP VISIT1 01 70
001 DBP VISIT1 . .
001 WEIGHT VISIT1 01 70
001 WEIGHT VISIT1 02 80
002 PULSE VISIT1 . .
002 PULSE VISIT1 02 80
002 DBP VISIT1 01 70
002 DBP VISIT1 02 80
002 WEIGHT VISIT1 . .
002 WEIGHT VISIT1 02 80
	;
	run;
proc sql noprint;
  create table output as
  select *, count(subject) as count
  from my_data
  where value ^= .
  group by subject, test, visit  
  ;
quit;

I am getting below output from above proc sql code:

But i want below out put with out losing observations to count VISIT values for each subejct by TEST where  VALUE will be not missing.

Expected output:

How to do in proc sql to get above output.

Thank you,

Rajasekhar.

[Tom · Accepted solution]

Are you trying to count observations or distinct subjects? 

If you want to count distinct subjects use the DISTINCT keyword.

count(distinct subject)

If you want to ignore the subjects with missing values use a CASE clause, not a WHERE clause

count(distinct case when (not missing(value)) then subject end)

[Ksharp]

proc sql ;
  create table output as
  select *, count(value) as count
  from my_data
  group by subject, test, visit  
  ;
quit;