topic Re: proc sql count for group and where clause without losing observations in SAS Programming

proc sql count for group and where clause without losing observations

raja777pharma — Sun, 10 Apr 2022 03:46:40 GMT

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.

Re: proc sql count for group and where clause without losing observations

Tom — Sun, 10 Apr 2022 05:03:24 GMT

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)

Re: proc sql count for group and where clause without losing observations

Ksharp — Sun, 10 Apr 2022 11:05:33 GMT


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