How to compare cross values on different columns SAS?

Question

I am trying to compare cross values on different date columns.Flag column has two values : D and R. My conditions are when D-Flag comes after R-flag and DATE2 equals to DATE1 create column named False_Flag and append 1 else append 0 by ID.

Here is a example of my table :

At ID=1, DATE2 on first row equals to DATE1 on second column and D flag comes after R flag so I want to create new column named False_flag and append 1 for this situation. And same for ID =3,for other situations like ID = 5 append 0 for False_flag


ID     DATE1        DATE2      FLAG 

1      10JUN2021    17JUN2021   R
1      17JUN2021    25SEP2021   D
3      12MAR2018    25MAR2018   R
3      25MAR2018    14APR2018   D
3      14APR2018    23JUL2018   D      
5      22OCT2020    15NOV2020   D

And here is What I want for output:

ID     DATE1        DATE2      FLAG  FALSE_FLAG

1      10JUN2021    17JUN2021   R      1
1      17JUN2021    25SEP2021   D      1
3      12MAR2018    25MAR2018   R      1
3      25MAR2018    14APR2018   D      1
3      14APR2018    23JUL2018   D      0
5      22OCT2020    15NOV2020   D      0

I am pretty new in SAS and How can I do that ?

I don't understand the logic. Also do you really need to go back and modify the row with the "R" after you have already moved onto the following row with the "D"? SAS is terrible at seeing the future but can be taught to remember the past. — Tom

Arun Arun · Accepted Answer · 2021-10-25T15:20:40

Here is how we can do it.

First, we must sort the data in particular order
Because we would be looking into subsequent records to get us the output
Unfortunately there is no LEAD function in SAS to get the next row's value
Hence we would sort it in opposite order and then use LAG function then bring it back to the actual order we want

Below code will sort the data in opposite order (descending)

Proc sort data=inp;
by descending id descending date1 descending date2 descending flag;
run;

Then finding next row's date1 and flag by using LAG function

Data lead_tbl;
set inp;
format lag_dt date9.;
lag_dt = lag(date1);   /* --> Next row's date 1*/
lag_flg = lag(flag);   /* --> Next row's flag*/
run;

Next, we would be sorting it back to the original order we want. Note- This step is crucial as this order determines the result.

Proc sort data=lead_tbl;
by id date1 date2 descending flag;  /* --> Descending flag is used to order it like R and D */
run;

Finally, creating false_flag using perfectly sorted lead_tbl table

/* Keep will just keep those columns */
data results(keep=id date1 date2 flag false_flag);
set lead_tbl;
by id;
retain pre_flag;

/* Checking if the row meets the criteria */
IF (first.id) and (date2 = lag_dt) and (flag='R') and (lag_flg='D') then do
    false_flag = 1;
    pre_flag = flag; 
end;

/* To update the second row for same id */
else if flag= 'D' and pre_flag = 'R' then do;
    false_flag = 1;
    pre_flag= flag;
end;

/* If record does not meet the criteria */
else do
    false_flag=0;
end;
run;

How to compare cross values on different columns SAS?

1 Answers