Hi guys,
suppose to have the following dataset:
data DB1;
input ID :$20. Date :date9.;
format Date date9.;
cards;
0001 16FEB2019
0002 12JAN2017
0002 22FEB2017
0002 27APR2017
0002 30JAN2019
0003 03MAR2019
..........
;
Is there a way to ad a column "Index" that has 1 if the date is the first chronologically for each patient and 0 otherwise?
If the patient has only one date, the Index should be 1 as in the other cases.
Desired Output:
0001 16FEB2019 1
0002 12JAN2017 1
0002 22FEB2017 0
0002 27APR2017 0
0002 30JAN2019 0
0003 03MAR2019 1
the dataset has around 30.000 rows. Moreover, this should be done from the start (first date that appears) to 30 days prior to the end (last date). In other words: suppose the end of the study is 31DEC2022 (I know exactly the date), if the first date for a patient is 11NOV2022 -->, since 11NOV2022 is < 90 days to 31DEC2022 a different index, e.g., 2 should be used to indicate this behaviour.
Can anyone help me please?
Thank you in advance
data want;
set db1;
by id;
if first.id and date<='30SEP2022'd then flag=1;
else if first.id and date>'30SEP2022'd then flag=2
else flag=0;
run;
Are the dates by ID in increasing chronological order? If not, then sort the data by ID and date.
data want;
set db1;
by id;
if first.id then flag=1;
else flag=0;
run;
I don't understand this part, and it would help if you explain further and include an example in the sample data set.
Moreover, this should be done from the start (first date that appears) to 30 days prior to the end (last date).
I didn't look carefully, did you include example data where we would need to take into account the last date for the patient?
data want;
set db1;
by id;
if first.id and date<='30SEP2022'd then flag=1;
else if first.id and date>'30SEP2022'd then flag=2
else flag=0;
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.