topic find the first record the value is 1 and lasted 24 hours in SAS Procedures

find the first record the value is 1 and lasted 24 hours

ShanshanCheng — Thu, 16 May 2024 08:33:58 GMT

hello, I want to find the first record: the value is 1 and lasted 24 hours, like my picture：

in obs 1,the value is 1 and the record after 24h is obs 4，but between these two records is a record with AVALCN=0,so obs 1 is N;

in obs 2 ,the value is 1 and the record after 24h is obs 5，but between these two records is a record with AVALCN=0,so obs 2 is N;

in obs 3,AVALCN=0 ,,so obs 2 is N;

in obs 4,,the value is 1 and the record after 24h is obs 6，the value between these two records is 1 ,so obs 4 is Y;

in obs 5,the value is 1 ，no records were obtained after 24h,so obs 5 is N;

Re: find the first record the value is 1 and lasted 24 hours

yabwon — Thu, 16 May 2024 09:32:39 GMT

You can use a hash table to do "look forward" your data.

I assume you probably need it in groups by subjectid, so I added them in example code.

data have;
  do subjectID = 1 to 3;
    do obs =1 to 15;
      avalcn=ranuni(42)<0.8;
      adtm + 7*60*60+17;
      output; 
    end;
  end;
  format adtm e8601dt20.;
run;
proc print;
run;

data want;
  declare hash H();
  H.defineKey("i");
  H.defineData("v","d","l");
  H.defineDone();
  do until(eof);
    set have end=eof;
    by subjectID;
    i+1;
    H.add(key:i, data:avalcn, data:adtm, data:last.subjectID);
  end;


  do until(eof1);
    set have end=eof1 curobs=curobs;
    by subjectID;

    flag="N";
    l=0;
    v=avalcn; 
    d=adtm;
    if not Found then
      do i=curobs by 1 until(l);
        H.find(key:i);
        if 0=v then leave;
        if abs(adtm-d)>86400 then 
          do;
            flag="Y";
            Found = 1;
            /* obs_contributed_to_flagY=i; */
            leave;
          end;
      end;

    output;
    if last.subjectID then Found = 0;
  end;
stop;
drop i v d l Found;
run;
proc print;
run;

Bart

Re: find the first record the value is 1 and lasted 24 hours

yabwon — Thu, 16 May 2024 09:44:38 GMT

Version with arrays just for completeness:

data _null_;
  call symputX("N",n);
  stop;
  set have nobs=n;
run;
data want2;
  
  array v[&n.] _temporary_;
  array d[&n.] _temporary_;
  array l[&n.] _temporary_;
  do until(eof);
    set have end=eof curobs=i;
    by subjectID;
    
    v[i] = avalcn; 
    d[i] = adtm;
    l[i] = last.subjectID;
  end;


  do until(eof1);
    set have end=eof1 curobs=curobs;
    by subjectID;

    flag="N";
    if not Found then
      do i=curobs by 1 until(l[i]);
        if 0=v[i] then leave;
        if abs(adtm-d[i])>86400 then 
          do;
            flag="Y";
            Found = 1;
            /* obs_contributed_to_flagY=i; */
            leave;
          end;
      end;

    output;
    if last.subjectID then Found = 0;
  end;
stop;
drop Found;
run;
proc print;
run;

Bart

Re: find the first record the value is 1 and lasted 24 hours

Patrick — Thu, 16 May 2024 10:37:16 GMT

@ShanshanCheng Make it a habit to provide sample data as text - ideally shared via a data step that creates it. Screenshots are not very useful if you're after tested code as solution.

Does below return what you're after?

data have;
  infile datalines truncover dlm=',' dsd;
  input subject_id:$5. avalcn:best32. adtm:e8601dt.;
  format adtm datetime20.;
  datalines;
A,1,2024-04-14T01:36
A,1,2024-04-14T08:36
A,0,2024-04-14T20:05
A,1,2024-04-15T08:38
A,1,2024-04-15T20:03
A,1,2024-04-16T08:50
B,1,2024-05-14T01:36
B,1,2024-05-14T08:36
B,1,2024-05-15T08:38
B,1,2024-05-15T20:03
B,1,2024-05-16T08:50
;

data want(drop=_:);
  set have nobs=_nobs;
  by subject_id adtm;
  if avalcn=0 then return;
  retain _start_obs;
  _start_obs=max(_start_obs,_n_+1);

  do i=_start_obs to _nobs;
    set have(keep=subject_id avalcn adtm
             rename=(subject_id=_subject_id avalcn=_avalcn adtm=_adtm))
            point=i;
    if _avalcn=0 or _subject_id ne subject_id then leave;
    if _adtm-adtm>=24*3600 then
      do;
        select_flag=1;
        _start_obs=i;
        leave;
      end;
  end;
run;

proc print data=want;
run;

Re: find the first record the value is 1 and lasted 24 hours

ShanshanCheng — Fri, 17 May 2024 03:14:01 GMT

Sorry ,the next question I'll use datalines provide test data instead of screenshots，thank you for your answer and solving my problem.