topic Re: populating previous date not working in SAS Programming

populating previous date not working

abraham1 — Thu, 26 Sep 2024 11:01:24 GMT

Hi,

From below dataset, I want to populate previous date for unique subjid. I use below sas code but it is not working. can you please help me.

I need to populate

1) prev_date as separate column

2)A flag1 column that populate 1 when difference between first visit date and last visit date >25.

2)A flag2 column that populate 1 when difference between first visit and previous visit date >25.

data demon;
    input subjid $ visitdate : yymmdd10.;
    format visitdate yymmdd10.;
    datalines;
111 2024-07-15
111 2024-07-22
111 2024-08-29
222 2024-07-15
222 2024-07-22
333 2024-07-24
333 2024-08-15
333 2024-09-25

;
run;
proc sort data=demon;by subjid visitdate;run;
data demo1;
    set demon;
    by subjid visitdate;
    retain prev_date;
    
    if first.subjid then do;
        prev_date = .; 
    end;
    else prev_date = lag(visitdate);  
    
    format prev_date yymmdd10.;
run;

Re: populating previous date not working

Kurt_Bremser — Thu, 26 Sep 2024 11:55:52 GMT

LAG will only populate its queue when it is called, so you must restructure your IF:

prev_date = lag(visitdate);
if first.subjid then prev_date = .;

Re: populating previous date not working

rudfaden — Thu, 26 Sep 2024 11:55:58 GMT

You do not lag the first obs.

Try this:

data demon;
    input subjid $ visitdate : yymmdd10.;
    format visitdate yymmdd10.;
    datalines;
111 2024-07-15
111 2024-07-22
111 2024-08-29
222 2024-07-15
222 2024-07-22
333 2024-07-24
333 2024-08-15
333 2024-09-25

;
run;
proc sort data=demon;by subjid visitdate;run;
data demo1;
    set demon;
    by subjid visitdate;
    retain prev_date;

    prev_date = lag(visitdate);

    if first.subjid then do;
        prev_date = .; 
    end;
      
    
    format prev_date yymmdd10.;
run;

Re: populating previous date not working

Kurt_Bremser — Thu, 26 Sep 2024 11:57:52 GMT

And since you set prev_date in every observation, the RETAIN is not necessary.

Re: populating previous date not working

rudfaden — Thu, 26 Sep 2024 12:15:10 GMT

If you want the flags, you can do this

data demon;
    input subjid $ visitdate : yymmdd10.;
    format visitdate yymmdd10.;
    datalines;
111 2024-07-15
111 2024-07-22
111 2024-08-29
222 2024-07-15
222 2024-07-22
333 2024-07-24
333 2024-08-15
333 2024-09-25

;
run;
proc sort data=demon;by subjid visitdate;run;
data demo1;
    set demon;
    by subjid visitdate;
    retain prev_date;

    
    if first.subjid then do;
        prev_date = visitdate;
    end;

    if not first.id then do;
        if visitdate-lag(visitdate)>25 then flag2=1;;
    end;

    if last.subjid then do;
        if visitdate-prev_date>25 then flag1=1;
    end;
    

    format prev_date yymmdd10.;
run;