topic Re: Flagging all the records within a id if atleast one record meets the predefined criterion in SAS Programming

Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Tue, 21 Feb 2023 22:09:50 GMT

Predefined criteria is flag the records for the id as Y if atleast one record with pstbsfl have result >=4*baseresult.
Data have;
Input Id visitn testcd result baseresult basefl pstbsfl

1 1 ALT 4 2 . Y
1 2 ALT 8 2 . Y
1 0 ALT 2 2 Y .
1 1 AST 2 2 . Y

;
Run;

Data want;
Id visitn testcd result baseresult basefl pstbsfl critfl

1 1 ALT 4 2 . Y Y
1 2 ALT 8 2 . Y Y
1 0 ALT 2 2 Y . Y
1 1 AST 2 2 Y . Y
;
Run;

The logic here is that as the id 1 is having atleast one record where result is 4* baseresult value ( visitn=2) I want to flag all the records for that id as critfl eq ‘Y’

Your response is appreciated.

Please help

Thanks

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

MayurJadhav — Tue, 21 Feb 2023 23:35:30 GMT

You can use count(*) on your have dataset with the condition and assign flag accordingly.

data have;
	Input Id visitn testcd $ result baseresult basefl $  pstbsfl $;
	datalines;

1 1 ALT 4 2 . Y
1 2 ALT 8 2 . Y
1 0 ALT 2 2 Y .
1 1 AST 2 2 . Y

;
run;

/* count how many records do you have with your if condition */
proc sql;
	select count(*) into:cnt_flg from want where result >=4*baseresult;
	%put cnt_flg= &cnt_flg;
quit;

/* flag Y/N on critfl */
data want;
	set have;

	if &cnt_flg >=1 then
		critfl='Y';
	else
		critfl='N';

proc print;
run;

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

s_lassen — Wed, 22 Feb 2023 08:53:54 GMT

Make sure the data is sorted by ID, and then this:

data want;                                                                                                                              
  if 0 then set have; /* just to get same order of variables */                                                                         
  critfl='N';                                                                                                                           
  do until(last.id);                                                                                                                    
    set have;                                                                                                                           
    by id;                                                                                                                              
    if result>=4*baseresult then                                                                                                        
      critfl='Y';                                                                                                                       
    end;                                                                                                                                
  do until(last.id);                                                                                                                    
    set have;                                                                                                                           
    by id;                                                                                                                              
    output;                                                                                                                             
    end;                                                                                                                                
run;

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Wed, 22 Feb 2023 10:11:27 GMT

Thank you so much for the earlier response , just to add one more detail to my question my criterion is based on one test i.e ALT and if that test is not available for a ID Then the critfl should be left blank and not as "N".

Thanks in advance.

Your response is appreciated.

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

s_lassen — Wed, 22 Feb 2023 10:18:16 GMT

I do not quite understand your question, could you give some example data?

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Wed, 22 Feb 2023 10:35:46 GMT

Data have;

input id visitn test result baseresult basefl postbasefl;

1 1 ALT 12 3 . Y

1 2 AST 3 1 .Y

2 1 AST 3 1 Y .

;

run;

Data want;

input id visitn test result baseresult basefl postbasefl critfl;

1 1 ALT 12 3 . Y Y

1 2 AST 3 1 .Y Y

2 1 AST 3 1 Y . .

;

run;

For the ID 2 as we dont see any ALT at all that satisfies the predefined criteria result>=4*baseresult i want to see CRITFL as missing for the ID throughout.

PREDEFINED CRITERION: If any postbasefl result of an ID with ALT Is ge 4*baseresult then flag all the records for that ID or if postbasefl for the ID for ALT is missing then leave a null value in CRITFL.

Hopefully I am a bit clear now

THANKS ALOT

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

s_lassen — Wed, 22 Feb 2023 10:55:10 GMT

Something like this?

data want;                                                                                                                              
  if 0 then set have; /* just to get same order of variables */                                                                         
  do until(last.id);                                                                                                                    
    set have;                                                                                                                           
    by id;                                                                                                                              
    if testcd='ALT' then do;                                                                                                            
      if result>=4*baseresult then                                                                                                      
        critfl='Y';                                                                                                                     
      else if critfl=' ' then                                                                                                           
        critfl='N';                                                                                                                     
      end;                                                                                                                              
    end;                                                                                                                                
  do until(last.id);                                                                                                                    
    set have;                                                                                                                           
    by id;                                                                                                                              
    output;                                                                                                                             
    end;                                                                                                                                
run;

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Wed, 22 Feb 2023 11:07:25 GMT

SO THE CRITFL SHOULD BE "Y" if there is any postbasefl record for the ID with ALT Result>=4*baseresult.
2.CRITFL should be "N" if there is no postbasefl record for the ID with ALT result NOT ge baseresult.
3.CRITFl should be "" (missing) if the ID Doesnt have any postbasefl record for the ALT test even if it has AST test.

The above response made the values NULL When the CRITFL should be "N".

Thanks once again for your prompt response.

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

Ksharp — Wed, 22 Feb 2023 11:51:37 GMT

data have;
 Input Id visitn testcd $ result baseresult basefl $  pstbsfl $;
 datalines;
1 1 ALT 4 2 . Y
1 2 ALT 8 2 . Y
1 0 ALT 2 2 Y .
1 1 AST 2 2 . Y
;
run;

proc sql;
create table want as
select *,ifc(max(result>=4*baseresult),'Y','N') as critfl
 from have
  group by id;
quit;

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Wed, 22 Feb 2023 12:13:25 GMT

@Ksharp thanks for your response the response is not giving NULL values for the ID If the ID Doesnt have any postbaseresult record for ALT.

Thanks alot

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Wed, 22 Feb 2023 12:15:16 GMT

these are my conditions to flag the CRITFL.
SO THE CRITFL SHOULD BE "Y" if there is any postbasefl record for the ID with ALT Result>=4*baseresult.
2.CRITFL should be "N" if there is no postbasefl record for the ID with ALT result NOT ge baseresult.
3.CRITFl should be "" (missing) if the ID Doesnt have any postbasefl record for the ALT test even if it has AST test.

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

Ksharp — Wed, 22 Feb 2023 12:32:12 GMT

OK.Try this one.(I did not test it)

P.S. You'd better post more output data to explain your question.

data have;
 Input Id visitn testcd $ result baseresult basefl $  pstbsfl $;
 datalines;
1 1 ALT 4 2 . Y
1 2 ALT 8 2 . Y
1 0 ALT 2 2 Y .
1 1 AST 2 2 . 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<baseresult)=0 then 'N'
     when max(pstbsfl='Y' and testcd='ALT')=0 then ' '
else 'X' end as critfl
 from have
  group by id;
quit;

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Wed, 22 Feb 2023 12:54:35 GMT

@Ksharp

Thanks for your response again.

Data want;

input id visitn test result baseresult basefl postbasefl critfl;

1 1 ALT 12 3 . Y Y

1 2 AST 3 1 .Y Y

2 1 AST 3 1 Y . .

2 2 AST 2 1 . Y .

;

run;

For the ID 2 as we dont see any ALT at all that satisfies the predefined criteria result>=4*baseresult i want to see CRITFL as missing for the ID throughout.

the above response has populated "X' for CRITFL when none of the pstblfl records has result>=4*baseresult but whereas we need to see "N",please have a look the want dataset above .

Thanks

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

Ksharp — Thu, 23 Feb 2023 11:53:07 GMT

/*
It is hard to understand your logic.
Can you post tree example/output corresponding to your three conditions?
*/
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)=0 then ' '
  when max(pstbsfl='Y' and testcd='ALT' and result<baseresult)=0 then 'N'
else 'X' end as critfl
 from have
  group by id;
quit;

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Fri, 24 Feb 2023 13:08:38 GMT

@Ksharp thanks for your response.

here is what i want:

Data want;

input $id visitn $test result baseresult $basefl $postbasefl $critfl;

1 1 ALT 12 3 . Y Y

1 2 AST 3 1 .Y Y

2 1 AST 3 1 Y . .

2 2 AST 2 1 . Y .

;

run;

LOGIC:

1.FOR CRITfl eq "Y"-THE RECORDS FOR ID HAVE BOTH ALT AND AST TESTS AND THE CRITFL SHOULD BE FLAGGED "Y" FOR ALL THOSE RECORDS OF THE ID IF THE ID HAS ATLEAST ONE RECORD WITH POSTBASEFL AS "Y" AND RESULT>=4*BASERESULT.

2. FOR CRITFL EQ "N" -FOR ID 1 IT SHOULD BE FLAGGED "N" if there is no record at all where postbasefl eq "Y" and result>=4*baseresult..

3.FOR CRITFL eq ""- IF THE ID DOESNT HAVE ANY POSTBASEFL EQ "Y" RECORD FOR THE TEST ALT THEN THE ID SHOULD HAVE CRITFL "".

HERE IS THE FLOWCHART IN CASE IF THE LOGIC IS STILL NOT CLEAR

Your Response is much appreciated.

TIA

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

Ksharp — Sat, 25 Feb 2023 11:11:07 GMT

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;

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Tue, 28 Feb 2023 11:18:40 GMT

@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.

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

Ksharp — Tue, 28 Feb 2023 12:00:58 GMT

/*
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;

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

sandhya88 — Tue, 28 Feb 2023 13:07:43 GMT

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

Re: Flagging all the records within a id if atleast one record meets the predefined criterion

Ksharp — Wed, 01 Mar 2023 14:45:18 GMT

/*
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.