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: Call for Content

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!

Submit your idea!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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