Assigning a count value based on distinct values

[smackerz1988]

Hello,

I need to assign an analysis flag based partly on this "If a subject has both "High" and "Low" (in ANRIND) within a parameter, set to "Y" for the earliest occurrence of each value.". I am using PROC SQL to achieve this using the following code.

proc sql;
   create table want as
   select *, count(wrstdate) as count
   from have
   where wrstflg is not null 
   group by subjid,paramcd;
quit;

This code is not overly important to understand but I'm trying to add this line of code

sum (distinct anrind in ('High','Low') as hlcnt) 

after count so I get a hlcnt of 2 so I can use it to keep these records when assigning a flag further downstream in the code. However, when I try this it partially works but it also keeps records when there is multiple values of high in different visits or same with multiple values of low which I don't want. I only want to keep it for subjects that have distinct value of high and low.

data have;
input  SUBJID $ PARAMCD $ VISIT :$30. ANRIND $ WRSTDATE $ WRSTCASE :3. WRSTFLG $; 
infile datalines dlm = '|';
datalines;
1001|AAA|Week 2 |Low|11JUL2022|2|Y
1001|AAA|Week 16|High|11JUL2022|3|Y
1002|BBB|Week 2 |Low|1AUG2022|2|Y
1002|BBB|Week 4 |Low|15AUG2022|2|Y
1003|CCC|Week 2 |High|1AUG2022|3|Y
1003|CCC|Week 4 |High|15AUG2022|3|Y
;
run;

data want;
input  SUBJID $ PARAMCD $ VISIT :$30. ANRIND $ WRSTDATE $ WRSTCASE :3. WRSTFLG $ HLCNT :3.; 
infile datalines dlm = '|';
datalines;
1001|AAA|Week 2 |Low|11JUL2022|2|Y|2
1001|AAA|Week 16|High|11JUL2022|3|Y|2
1002|BBB|Week 2 |Low|1AUG2022|2|Y|1
1002|BBB|Week 4 |Low|15AUG2022|2|Y|1
1003|CCC|Week 2 |High|1AUG2022|3|Y|1
1003|CCC|Week 4 |High|15AUG2022|3|Y|1
;
run;

[PGStats]

I think what you want is

count (distinct whichc(anrind, 'High', 'Low')) as hlcnt

[smackerz1988]

Hi. It's on the right track but not quite right. There are other values in anrind ( e.g 'Normal') and it seems to pick that up in the count. 

[PGStats]

And what do you want as a result in such cases?

[smackerz1988]

I only want it to pick up a count for distinct values of either 'High' or 'Low' only in any visit per paramcd

[PGStats · Accepted solution]

Then try

count (distinct case anrind when 'High' then 1 when 'Low' then 2 else . end) as hlcnt

[smackerz1988]

That's it! Perfect thank you very much for your help!