I think your LOGIC doesn't match your FLOWCHART.
"FOR ID HAVE BOTH ALT AND AST TESTS "
but it didn't appear in your FLOWCHART.
Anyway,try this one :
data have;
Input Id visitn testcd $ result baseresult basefl $ pstbsfl $;
datalines;
1 1 ALT 12 3 . Y
1 2 AST 3 1 . Y
2 1 AST 3 1 Y .
2 2 AST 2 1 . Y
;
run;
proc sql;
create table want as
select *,
case when max(pstbsfl='Y' and testcd='ALT' and result>=4*baseresult) then 'Y'
when max(pstbsfl='Y' and testcd='ALT' and result<4*baseresult) then 'N'
when max(pstbsfl='Y' and testcd='ALT' and result>=4*baseresult)=0 then ' '
else 'X' end as critfl
from have
group by id;
quit;
/* If you think there are something wrong in my code, I think you'd better post an example to illustrate, It is hard to understand what is your mean by literally. Anyway, Try this code. If the result is not right,POST AN EXAMPLE. */ data have; Input Id visitn testcd $ result baseresult basefl $ pstbsfl $; datalines; 1 1 ALT 12 3 . Y 1 2 AST 3 1 . Y 2 1 AST 3 1 Y . 2 2 AST 2 1 . Y ; run; proc sql; create table want as select *, case when max(pstbsfl='Y' and testcd='ALT' and result>=4*baseresult) then 'Y' when max(pstbsfl='Y' and testcd='ALT' and result<4*baseresult) then 'N' when max(testcd='ALT')=0 then ' ' else 'X' end as critfl from have group by id; quit;
ok this is how i want the data to be finally if that helps:
YELLOW HIGHLIGHTED: CRITFL-Y FOR ALL THE RECORDS OF THE ID AS THE ID 004 HAS ATLEAST ONE ALT PSTBSFL RECORD WITH RESULT>=4*BASERSLT
GREEN HIGHLIGHTED: CRITFL-N FOR ALL THE RECORDS OF ID 005 AS THERE IS NO PSTBSFL ALT RECORD WITH RESULT>=4*BASERSLT
PEACH HIGHLIGHTED: CRITFL-. FOR ALL THE RECORDS OF THE ID 006 AS THERE ARE NO PSTBSFL ALT RECORDS AT ALL.
Thanks
/* OK. Test the following code. If there was something wrong,post an example. */ data have; Input Id visitn testcd $ result baseresult basefl $ pstbsfl $; datalines; 1 1 ALT 12 3 . Y 1 2 AST 3 1 . Y 2 1 AST 3 1 Y . 2 2 AST 2 1 . Y ; run; proc sql; create table want as select *, case when max(pstbsfl='Y' and testcd='ALT') and max(pstbsfl='Y' and testcd='ALT' and result>=4*baseresult) then 'Y' when max(pstbsfl='Y' and testcd='ALT') and max(pstbsfl='Y' and testcd='ALT' and result>=4*baseresult)=0 then 'N' when max(pstbsfl='Y' and testcd='ALT')=0 then ' ' else 'X' end as critfl from have group by id; quit;
EDITED.
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.