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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.