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;
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;
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.
I do not quite understand your question, could you give some example data?
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
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;
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;
@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
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;
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.
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.
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
/*
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;
@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
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.