Hi,
I have the following dataset. I flagged the observation that has HbA1c <6.5 and if months is less or equal to 6 months.
patid | hba1c | months | flag |
1 | 6.6 | 0 | 0 |
1 | 6.1 | 3 | 1 |
1 | 7.9 | 5 | 0 |
1 | 6.3 | 3 | 1 |
2 | 6.6 | 0 | 0 |
2 | 6.4 | 4 | 1 |
2 | 6.4 | 3 | 1 |
3 | 6.4 | 0 | 1 |
3 | 8.8 | 6 | 0 |
3 | 5.3 | 3 | 1 |
3 | 5.5 | 4 | 1 |
3 | 3.2 | 6 | 1 |
3 | 7 | 8 | 0 |
3 | 7.5 | 3 | 0 |
3 | 7.6 | 5 | 0 |
However, when there is more than one consecutive flag (1), I want to keep the first Flag (1) as 1 (remission) and the following 1's as 0 (remission) until the next value is 0 (Highlighted red), by patid.
Want:
patid | hba1c | months | flag | remission |
1 | 6.6 | 0 | 0 | 0 |
1 | 6.1 | 3 | 1 | 1 |
1 | 7.9 | 5 | 0 | 0 |
1 | 6.3 | 3 | 1 | 1 |
2 | 6.6 | 0 | 0 | 0 |
2 | 6.4 | 4 | 1 | 1 |
2 | 6.4 | 3 | 1 | 0 |
3 | 6.4 | 0 | 1 | 1 |
3 | 8.8 | 6 | 0 | 0 |
3 | 5.3 | 3 | 1 | 1 |
3 | 5.5 | 4 | 1 | 0 |
3 | 3.2 | 6 | 1 | 0 |
3 | 7 | 8 | 0 | 0 |
3 | 7.5 | 3 | 0 | 0 |
3 | 7.6 | 5 | 0 | 0 |
Could anyone please help me with the code? I tried to search but I don't seem to get the right keyword for search. Thanks.
Sandyzman1
data want;
set have;
by patid;
if hba1c < 6.5 and months <= 6 then flag = 1;
remission = flag;
if not first.patid and lag(flag) = 1 then remission = 0;
run;
patid hba1c months flag remission 1 6.6 0 0 0 1 6.1 3 1 1 1 7.9 5 0 0 1 6.3 3 1 1 2 6.6 0 0 0 2 6.4 4 1 1 2 6.4 3 1 0 3 6.4 0 1 1 3 8.8 6 0 0 3 5.3 3 1 1 3 5.5 4 1 0 3 3.2 6 1 0 3 7.0 8 0 0 3 7.5 3 0 0 3 7.6 5 0 0
data want;
set have;
by patid;
if hba1c < 6.5 and months <= 6 then flag = 1;
remission = flag;
if not first.patid and lag(flag) = 1 then remission = 0;
run;
patid hba1c months flag remission 1 6.6 0 0 0 1 6.1 3 1 1 1 7.9 5 0 0 1 6.3 3 1 1 2 6.6 0 0 0 2 6.4 4 1 1 2 6.4 3 1 0 3 6.4 0 1 1 3 8.8 6 0 0 3 5.3 3 1 1 3 5.5 4 1 0 3 3.2 6 1 0 3 7.0 8 0 0 3 7.5 3 0 0 3 7.6 5 0 0
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.