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;
Then try
count (distinct case anrind when 'High' then 1 when 'Low' then 2 else . end) as hlcnt
I think what you want is
count (distinct whichc(anrind, 'High', 'Low')) as hlcnt
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.
And what do you want as a result in such cases?
I only want it to pick up a count for distinct values of either 'High' or 'Low' only in any visit per paramcd
Then try
count (distinct case anrind when 'High' then 1 when 'Low' then 2 else . end) as hlcnt
That's it! Perfect thank you very much for your help!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.