BookmarkSubscribeRSS Feed
Ksharp
Super User

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;
sandhya88
Calcite | Level 5
@Ksharp thanks for your response and definitely its not populating Y,N OR NULL
Values of an ID If the test is other than ALT.
SO HERE MY AIM IS TO POPULATE THE CRITFL WITH 'Y' FOR ALL(BOTH ALT,AST) THE RECORDS OF THE ID IF ATLEAST ONE RECORD IS HAVING ALT PSTBSFL AND ITS RESULT>=4*BASERESULT AND NULL FOR CRITFL IF THE ID DOESNT HAVE ANY RECORD WITH ALT AND CRITFL AS "N" IF THE ID HAS A ALT POSTBSFL RECORD BUT THE RESULT^>=4*BASERESULT.
Ksharp
Super User
/*
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;
sandhya88
Calcite | Level 5

ok this is how i want the data to be finally if that helps:

sandhya88_0-1677589544920.png

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

Ksharp
Super User
/*
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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 19 replies
  • 2411 views
  • 1 like
  • 4 in conversation