Quartz | Level 8

## Conditionally add an index based on a date

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

## Re: Conditionally add an index based on a date

``````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;``````
--
Paige Miller
6 REPLIES 6
Diamond | Level 26

## Re: Conditionally add an index based on a date

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).

--
Paige Miller
Quartz | Level 8

## Re: Conditionally add an index based on a date

Thank you very much! I will edit the post
Diamond | Level 26

## Re: Conditionally add an index based on a 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?

--
Paige Miller
Quartz | Level 8

## Re: Conditionally add an index based on a date

So, the last date is not required. The condition is: if the first date is > 30SEPT2022 (i.e., 90 days before the end of the study) then index = 2
Diamond | Level 26

## Re: Conditionally add an index based on a date

``````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;``````
--
Paige Miller
Quartz | Level 8

## Re: Conditionally add an index based on a date

Thank you very much!
Discussion stats
• 6 replies
• 392 views
• 1 like
• 2 in conversation