BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NewUsrStat
Pyrite | Level 9

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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
NewUsrStat
Pyrite | Level 9
Thank you very much! I will edit the post
PaigeMiller
Diamond | Level 26

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
NewUsrStat
Pyrite | Level 9
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
PaigeMiller
Diamond | Level 26
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
NewUsrStat
Pyrite | Level 9
Thank you very much!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 6 replies
  • 461 views
  • 1 like
  • 2 in conversation