topic Re: Assigning a count value based on distinct values in SAS Programming

Assigning a count value based on distinct values

smackerz1988 — Wed, 11 Jan 2023 18:54:35 GMT

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;

Re: Assigning a count value based on distinct values

PGStats — Wed, 11 Jan 2023 19:18:14 GMT

I think what you want is

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

Re: Assigning a count value based on distinct values

smackerz1988 — Wed, 11 Jan 2023 20:33:10 GMT

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.

Re: Assigning a count value based on distinct values

PGStats — Wed, 11 Jan 2023 20:46:50 GMT

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

Re: Assigning a count value based on distinct values

smackerz1988 — Wed, 11 Jan 2023 20:48:34 GMT

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

Re: Assigning a count value based on distinct values

PGStats — Wed, 11 Jan 2023 20:56:20 GMT

Then try

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

Re: Assigning a count value based on distinct values

smackerz1988 — Wed, 11 Jan 2023 21:02:24 GMT

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