Hi All,
I have a dataset that can be simplified to the following for the purpose of my question:
ID | Dispflag | DRDiagnose |
1 | 1 | |
2 | 3 | |
2 | 7 | |
2 | 1 | |
3 | 12 | |
3 | 1 | |
4 | 1 | |
5 | 2 | |
5 | 8 | |
5 | 1 | |
6 | 2 | |
6 | 1 | |
7 | 1 | |
8 | 1 | |
9 | 6 | |
9 | 14 | |
9 | 14 | |
9 | 1 |
for each ID I want to set the value of DRDiagnose to the previous value of DRDiagnose if the Dispflag is 1. If there is no previous value for DRDiagnose for that ID (like for ID 1, 7, or 😎 then the value will remain missing. Basically, I want to get the following dataset:
ID | Dispflag | DRDiagnose |
1 | 1 | |
2 | 3 | |
2 | 7 | |
2 | 1 | 7 |
3 | 12 | |
3 | 1 | 12 |
4 | 1 | |
5 | 2 | |
5 | 8 | |
5 | 1 | 8 |
6 | 2 | |
6 | 1 | 2 |
7 | 1 | |
8 | 1 | |
9 | 6 | |
9 | 14 | |
9 | 14 | |
9 | 1 | 14 |
I thought I can do it in a few lines of code but it is not working:
data want; set have; by ID; if (Dispflag eq 1) then DRDiagnose=lag(DRDiagnose); run;
Does anyone know why my code is not working and have a fix for it or another solution to achieve the "want" dataset? Many thanks in advance.
data have; infile cards truncover; input ID Dispflag DRDiagnose; cards; 1 1 2 . 3 2 . 7 2 1 3 . 12 3 1 4 1 5 . 2 5 . 8 5 1 6 . 2 6 1 7 1 8 1 9 . 6 9 . 14 9 . 14 9 1 ; data want ; set have ; lag=lag(DRDiagnose); if id=lag(id) and Dispflag then DRDiagnose=lag ; drop lag; run;
data have;
infile cards truncover;
input ID Dispflag DRDiagnose;
cards;
1 1
2 . 3
2 . 7
2 1
3 . 12
3 1
4 1
5 . 2
5 . 8
5 1
6 . 2
6 1
7 1
8 1
9 . 6
9 . 14
9 . 14
9 1
;
data want ;
set have ;
by id ;
retain t ;
if first.id then t = . ;
if DRDiagnose then t = DRDiagnose ;
if Dispflag and not DRDiagnose and t then DRDiagnose = t ;
drop t ;
run ;
Hi,
Thank you very much for your help. I tried this but it did not work. I was not able to fix it either. But using retain is an excellent idea. I was so stalked with lag that I did not even think of retain 😞
data have; infile cards truncover; input ID Dispflag DRDiagnose; cards; 1 1 2 . 3 2 . 7 2 1 3 . 12 3 1 4 1 5 . 2 5 . 8 5 1 6 . 2 6 1 7 1 8 1 9 . 6 9 . 14 9 . 14 9 1 ; data want ; set have ; lag=lag(DRDiagnose); if id=lag(id) and Dispflag then DRDiagnose=lag ; drop lag; run;
Hi,
Thank you so much. All that was needed was to add that dummy variable "lag" and drop it at the end. Very interesting.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.