Counting number of unique responses by row, across multiple variables

monsterpie — Tue, 21 Sep 2021 16:47:18 GMT

Hello all,

I am trying to count the number of responses across multiple variables. I would like to be able to output a new variable that counts the number of unique (i.e., the same value in one row should not be counted twice) values across diag_1 to diag_5 excluding any 'stress' or 'infection' values. Below is the code I have tried thus far based on another solution on this forum, I'm not sure how to finish adapting the code to give me the desired output.

data have;
input id diag_1 :$9. diag_2 :$9. diag_3 :$9. diag_4 :$9. diag_5 :$9. age;
cards;
01 diabetes stress . . . 42
02 stroke cancer stroke infection death 78
03 stroke copd . copd . 66
;
run;

data want;
set have;
array diag {5} diag_1-diag_5;
array new {5} $9  _temporary_;
do _n_=1 to 5;
   new{_n_} = diag{_n_};
end;
call sortc(of new{*});
count = (new{1} > ' ');
do _n_=2 to 5;
   if new{_n_}  ne new{_n_-1} then count + 1;
end;
run;

Output of the above code:

id	diag_1	diag_2	diag_3	diag_4	diag_5	age	treatment	family	Count
1	diabetes	stress				42	1	2	2
2	stroke	cancer	stroke	infection	death	78	1	2	4
3	stroke	copd		copd		66	2	1	2

Desired output:

id	diag_1	diag_2	diag_3	diag_4	diag_5	age	treatment	family	Count
1	diabetes	stress				42	1	2	1
2	stroke	cancer	stroke	infection	death	78	1	2	3
3	stroke	copd		copd		66	2	1	2

Re: Counting number of unique responses by row, across multiple variables

Oligolas — Tue, 21 Sep 2021 16:27:36 GMT

Well, you could check if the value of 'new' does not correspond to one of the terms to exclude before incrementing your count.

For my part, as counting proceeds rather vertically, i'd like to transpose the 'diags', count them and merge back the results:

data have;
input id diag_1 :$9. diag_2 :$9. diag_3 :$9. diag_4 :$9. diag_5 :$9. age;
cards;
01 diabetes stress . . . 42
02 stroke cancer stroke infection death 78
03 stroke copd . copd . 66
;
run;

PROC TRANSPOSE data=have out=hlp1;
   by id;
   var diag_1-diag_5;
RUN;

PROC SQL;
   CREATE TABLE hlp2 AS
      SELECT id, count(DISTINCT col1) AS count
      FROM hlp1
      WHERE upcase(col1) NOT IN ('STRESS' 'INFECTION')
      GROUP BY id      
      ;
   CREATE TABLE want AS
      SELECT a.*,b.count
      FROM have a
      LEFT JOIN hlp2 b
      ON a.id eq b.id
    ;
    DROP TABLE hlp1,hlp2
    ;
QUIT;

topic Counting number of unique responses by row, across multiple variables in SAS Programming

Counting number of unique responses by row, across multiple variables

Re: Counting number of unique responses by row, across multiple variables