BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SAS_Learner2
Calcite | Level 5

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller
SAS_Learner2
Calcite | Level 5

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_Learner2_1-1683184315887.png

 

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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.

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
  • 2 replies
  • 279 views
  • 0 likes
  • 2 in conversation