Hi Folks,
I have data like below.
When the flag1 variable has "Y" and for the "N" records, we want to take last Flag1 = "Y" or Flag2 = "Y" record date1 value to date2 variable minus current date1 value is greater than 90 hours then flag2 value should be flagged as "Y".
then repeat the same logic for remaining records.
Below 1st record has Flag1 "Y" so I want to take this date1 record value compare with next Flag1 = "N" records.
For the 22AUG2022 record hour difference is greather than 90 hour so We want to get Flag2 as Y.
Then since this is Flag2 = "Y" for record 22AUG2022:02:30:00 I want to take this date1 value further for flag1 = "N" records.
Have:
I have data like below.
ID date1 Flag1
1 18AUG2022:07:26:00 Y .
1 19AUG2022:02:30:00 N
1 20AUG2022:03:00:00 N
1 21AUG2022:01:00:00 N
1 22AUG2022:02:30:00 N
1 23AUG2022:01:00:00 N
1 25AUG2022:02:30:00 N
1 26AUG2022:02:00:00 N
Want:
ID date1 Flag1 Date2 Diff_hr Flag2
1 18AUG2022:07:26:00 Y . .
1 19AUG2022:02:30:00 N 18AUG2022:07:26:00 19 N
1 20AUG2022:03:00:00 N 18AUG2022:07:26:00 43 N
1 21AUG2022:01:00:00 N 18AUG2022:07:26:00 65 N
1 22AUG2022:02:30:00 N 18AUG2022:07:26:00 91 Y
1 23AUG2022:01:00:00 N 22AUG2022:02:30:00 22 N
1 25AUG2022:02:30:00 N 22AUG2022:02:30:00 72 N
1 26AUG2022:02:00:00 N 22AUG2022:02:30:00 95 Y
data have;
input id date1 :datetime18. flag1 $;
cards;
1 18AUG2022:07:26:00 Y
1 19AUG2022:02:30:00 N
1 20AUG2022:03:00:00 N
1 21AUG2022:01:00:00 N
1 22AUG2022:02:30:00 N
1 23AUG2022:01:00:00 N
1 25AUG2022:02:30:00 N
1 26AUG2022:02:00:00 N
;
data want;
set have;
retain date2;
if flag1='Y' then date2=date1;
hours=(date1-date2)/3600;
flag2='N';
if hours>=90 then do;
date2=date1;
flag2='Y';
end;
run;
proc print data=want;
format date1 date2 datetime19.;
run;
So this works for the data provided. I suspect if there is an ID value of 2, you would want to re-start the process, but since you haven't mentioned that or provided data for that case, I leave the answer as is; under certain assumptions the code likely it works in that case as well, but alas no data to test it on.
data have;
input id date1 :datetime18. flag1 $;
cards;
1 18AUG2022:07:26:00 Y
1 19AUG2022:02:30:00 N
1 20AUG2022:03:00:00 N
1 21AUG2022:01:00:00 N
1 22AUG2022:02:30:00 N
1 23AUG2022:01:00:00 N
1 25AUG2022:02:30:00 N
1 26AUG2022:02:00:00 N
;
data want;
set have;
retain date2;
if flag1='Y' then date2=date1;
hours=(date1-date2)/3600;
flag2='N';
if hours>=90 then do;
date2=date1;
flag2='Y';
end;
run;
proc print data=want;
format date1 date2 datetime19.;
run;
So this works for the data provided. I suspect if there is an ID value of 2, you would want to re-start the process, but since you haven't mentioned that or provided data for that case, I leave the answer as is; under certain assumptions the code likely it works in that case as well, but alas no data to test it on.
Thanks,
Flag2 variable coming correct. but date2 variable is not having the expected value when Flag2 = "Y".
For the 5th row date2 should have value 18AUG2022:07:26:00.
How to store that value in separate/same variable ?
Please help
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.