topic Re: Conditionally add an index based on a date in New SAS User

Conditionally add an index based on a date

NewUsrStat — Thu, 18 Apr 2024 16:06:08 GMT

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

Re: Conditionally add an index based on a date

PaigeMiller — Thu, 18 Apr 2024 15:38:01 GMT

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

Re: Conditionally add an index based on a date

NewUsrStat — Thu, 18 Apr 2024 15:56:55 GMT

Thank you very much! I will edit the post

Re: Conditionally add an index based on a date

PaigeMiller — Thu, 18 Apr 2024 16:32:51 GMT

I didn't look carefully, did you include example data where we would need to take into account the last date for the patient?

Re: Conditionally add an index based on a date

NewUsrStat — Thu, 18 Apr 2024 17:00:17 GMT

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

Re: Conditionally add an index based on a date

PaigeMiller — Thu, 18 Apr 2024 17:24:16 GMT

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;

Re: Conditionally add an index based on a date

NewUsrStat — Thu, 18 Apr 2024 17:27:08 GMT

Thank you very much!