I'd like to calculate lagtime in admission dates between source 1 and 2. Below attempt results in lagtime variable created but missing all the way. Not calculated. Admission_date is a SAS date and numeric. I had no problem for substraction in data step in other programs.
Do you see where I mess up?
data demo;
input UID $ Source admission_date;
cards;
m12 1 20110
m12 2 20114
m13 1 20119
m13 2 20231
m14 1 20500
m14 2 20600
;
run;
proc sort data=demo;
by UID source;
run;
DATA demo1; SET demo;
BY UID source;
RETAIN R_admission_date;
IF FIRST.source THEN DO;
R_admission_date = admission_date;
END;
IF LAST.source THEN DO;
lagtime = admission_date - R_admission_date;
OUTPUT;
END;
DROP R_: ;
RUN;
Thank you very much for your time.
@Cruise wrote:
Even if i am interested in the difference between source 1 and 2 within same uid?
Yes.
data demo2;
set demo;
by uid source;
first_source= first.source;
last_source = last.source;
first_uid=first.uid;
last_uid=last.uid;
run;
proc print;run;
Obs | UID | Source <- UID + SOURCE uniquely identifies a record | admission_date | first_source | last_source | first_uid | last_uid |
---|---|---|---|---|---|---|---|
1 | m12 | 1 | 20110 | 1 | 1 | 1 | 0 |
2 | m12 | 2 | 20114 | 1 | 1 | 0 | 1 |
3 | m13 | 1 | 20119 | 1 | 1 | 1 | 0 |
4 | m13 | 2 | 20231 | 1 | 1 | 0 | 1 |
5 | m14 | 1 | 20500 | 1 | 1 | 1 | 0 |
6 | m14 | 2 | 20600 | 1 | 1 | 0 | 1 |
This program should give you all zero values (not all missing values).
The logic should condition on FIRST.UID and LAST.UID, not FIRST.SOURCE and LAST.SOURCE. The way it reads now, it takes the beginning and ending date from the same observation.
@Cruise wrote:
Even if i am interested in the difference between source 1 and 2 within same uid?
Yes.
data demo2;
set demo;
by uid source;
first_source= first.source;
last_source = last.source;
first_uid=first.uid;
last_uid=last.uid;
run;
proc print;run;
Obs | UID | Source <- UID + SOURCE uniquely identifies a record | admission_date | first_source | last_source | first_uid | last_uid |
---|---|---|---|---|---|---|---|
1 | m12 | 1 | 20110 | 1 | 1 | 1 | 0 |
2 | m12 | 2 | 20114 | 1 | 1 | 0 | 1 |
3 | m13 | 1 | 20119 | 1 | 1 | 1 | 0 |
4 | m13 | 2 | 20231 | 1 | 1 | 0 | 1 |
5 | m14 | 1 | 20500 | 1 | 1 | 1 | 0 |
6 | m14 | 2 | 20600 | 1 | 1 | 0 | 1 |
Thanks a lot. Below codes suggested worked out. It outputs half of the data with lagtime between same UID which I wanted. However, I'd like to retain whole dataset. For that purpose when I eliminate:
IF last.UID THEN OUTPUT;
output is not truncated to the last obs of UID but it contains lagtime between adjacent but different UIDs which i do not need. To get back to the whole dataset I merge the lagtime output data to the original one.
I wonder, if there is a way to output whole dataset with lagtime within same UID but no unnessecary lagtime calculated between different UIDs?
proc sort data=demo;
by UID;
DATA demo1; SET demo;
BY UID;
RETAIN R_admission_startdate;
IF FIRST.UID THEN DO;
R_admission_startdate = admission_startdate;
END;
IF LAST.UID THEN DO;
lagtime = admission_startdate - R_admission_startdate;
OUTPUT;
END;
DROP R_: ;
RUN;
Using lag function:
data demo1; set demo;
by UID;
LAGTIME = admission_startdate-lag(admission_startdate);
IF last.UID THEN OUTPUT;
run;
Thank you all.
My final code then is:
data have1(drop=admission_startdate); set have(keep=UID admission_startdate);
by UID;
LAGTIME = admission_startdate-lag(admission_startdate);
IF last.UID THEN OUTPUT;
run;
data want;
merge
doh.have (in=a)
doh.have1 (in=b);
by UID;
if a and b then output;
run;
If you have pairs of observations, the dif function may interest you.
data DEMO1;
set DEMO;
by UID ;
LAGTIME = dif(ADMISSION_DATE);
if last.UID then output;
run;
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.