Hi Jim,   I am so so so sorry for my delayed response. Our medical school's remote server had an issue in a change they made to improve internet browsing, which prevented users from accessing outside websites. The new platform has just be fixed. Now I am able to login my SAS community account and have a chance to try your new code.    Yeah!!! The code in your last response works very well!!! Hats off to you! You are a REAL SAS MASTER!!! I appreciate it a LOT!!! Without your help, my project couldn't be completed. Also, I really appreciate this learning experience. Again, I am very grateful for your GREAT instruction and advice!!!    Best regards, Cynthia ... View more

Hi Jim,   I think I know what is going on there. By using your code, SAS provides 0/1 for variable-Both from ID=1 until the max ID the dataset has, regardless whether any ID between 1 to max ID exists or not in the dataset. For example, if the dataset has ID=3, 6, 12, and 15 (only 4 people in the dataset), SAS will provide results for ID=1 to 15, even though 1, 2, 4, 5, 7, 8, 9, 10, 11, 13, and 14 are not in the dataset. To solve this issue, can I create a hash table including the IDs the dataset actually has and use that hash table to find the IDs that I am interested in from the result table?   I have another question. I am not very familiar with macro variable, so when I substitute your code with my actual variable name, SAS log showed apparent symbolic is not resolved. I think this issue happened when I was trying to replace drug 'A' and 'B' with my actual drug name. What else code should I adjust as well when I want to replace A with antidepressant and replace B with sedative? Should I replace the part inside of '&Db PUTLOG "&Nte1 Evaluating Drug A and Drug B tables"; '? I know I shouldn't have asked this simple question, I just want to make sure I understand your code.   I know I have raised so many questions on this project. I really don't want to lose this rare learning opportunity to extend my SAS skills! Again and Again, I really appreciate your help a lot!!!   Best regards, C     ... View more

Hi Jim,   I am so sorry for my late response.   I think your code that was posted on 9/26/2020 12:22pm matches best with my desired result. The only issue with this code is that the ID in my test data is from 1 and it is continuous, 1, 2, 3, and 4. However, the ID in my real data is not continuous and not start from 1. It is like 6, 10, 21, 23, 24, 29, and so on. How can I fix the code to accommodate it to my real data in terms of ID? Thank you so much!   Back to the table you put together by hand (I really appreciate that you did this for checking!!!). I think we have no problem with definition 1 since it is straightforward. I know definition 2 is very tricky and needs to have a better explanation. Let think in this way for definition 2.Drug A and B are not allowed to take simultaneously based on clinical guideline. Both drug A and B have a delayed effect (not an effect that could affect the previous drug), which means that blood concentration for each of them can still be tested even 3 days AFTER the last taking date of the other drug, whichever drug takes first. For ID=1, the actual effect for drug B is from 1/1/2017 to 7/2/2017 (3days after 6/29/2017). The actual effect of drug A of the 2nd prescription for ID=1 is from 2/1/2017 to 4/4/4/2017, the 3rd is from 3/1/2017 to 5/2/2017 (3 days after 4/29), and the 4th is from 4/1/2017 to 6/2/2017 (3 days after 5/20/2017). The actual period that drug B actually had effect on ID=1 covered the period that drug A was taking, and the overlapping use happened in 2017-02, 03, 04, 05, and 06, but not 2017-01. This is because ID=1 only took drug B but not drug A, so there was no overlapping use happened in 2017-01.  So, same for ID=4, the 1st prescription should be considered from 1/4/2017 to 3/3/2017, and the 3rd prescription should be from 3/7/2017 to 5/3/2017, so the overlapping use for ID=1 happened in 2017-02, 03, and 05. For ID=2, I think the overlapping use happened in 2017-06 and 07. For ID=3, I think the overlapping use happened in 2017-09, 10, 11 and 12. Do you agree with me?   I think one solution is to create a new variable titled last_taking_date_Ext by simply adding 3 to last_taking_take (last_taking_date_Ext=last_taking_date + 3), and this last_taking_date_Ext that represents the actual last date for a specific drug is going to be replaced all the last_taking_date in the code when applying definition 2. Does it make sense?   Many many thanks for your effort and time again!!! I really this great learning opportunity!!! Best regards, C ... View more

Hi Jim,   You don't know how much I appreciate your help with my project!!! It really makes my work moving forward!!! I tried your code for both definition 1 and definition 2 based on my dataset, and they all worked very well!   I took your advice to test the code with a new dataset. I am attaching the new dataset here. I found the code for definition 1 worked for the new data very correctly, however, it cannot provide the precise result when applying to definition 2 for ID=4 (for ID=1-3, it worked very well). For example, the overlapping uses for ID=4 should be 2/4/2017 to 3/3/2017 and 5/3/2017, that is the variable Both=1 for 2017-02, 03, and 05. But SAS only returned as 2017-02. I changed the %LET Extra_days=3 when testing the code for definition 2. I resolved this issue by creating a new variable called last_taking_date_Ext=last_taking_date + 3, that is although the actual last taking date is on 2/28/2017 or 4/30/2017, the effect of the drug lasts until 3/3/2017 and 5/3/2017. Is there any way to fix the code to let it more appropriate for my new dataset?   I know you have generously spent your time on my project for very long time, I sincerely appreciate your great advice and help!!!   Best regards, C ... View more

Hi Jim,   I am sincerely grateful for your continuous advice and help on this project! I appreciate it a lot!   To get back to the question. Yes, we need to go down to the day level. For ID=3, the 1st prescription (drug B) from 8/3/2017 to 9/1/2017 (please update the typo, it should be 2017 but 2018) has No overlapping use with the 2nd prescription (drug A) from 9/3/2017 to 10/2/2017 according to the definition 1. Having both drug A and drug B taking any time in a given month doesn't necessarily make any overlapping use happened. Having both of them in any given day does.    However, it was an overlapping use based on the definition 2 since ID=3 started taking drug A (9/3/2017) within 3 days after the last day of taking drug B (9/1/2017). If the 2nd prescription (drug A) started on 9/5/2017 (not as it is right now on 9/3/2017),  which is the 4th day after the last day of taking drug B, then it would NOT be considered as an overlapping use based on the definition 2.    Definition 1:     If interval<=0 day then overlapping use=Yes; if interval>=1 day then overlapping use=0. (Interval is the day difference in start_taking_date and last_taking_date between two different drugs) Definition 2:    If interval<=3 day then overlapping use=Yes; if interval>=4 day then overlapping use=0   I need two programs, one program for the definition 1 and a separate program for the definition 2.   Best regards, C ... View more

Hi Jim,   I really appreciate your prompt reply a lot!   Your code is working this time!!! I just found a typo that the first prescription for ID=3 should be 8/3/2017 to 9/1/2017 other than 8/3/2018 to 9/1/2018. So sorry for this mistake. Did you use my second definition to calculate the variable Both, that is if drug A and drug B are taken even in 3 days interval (including the scenario when they are actually taken on the same day), it is still considered as an overlapping use of both A and B? If not, that is you are using the first definition, shouldn't it be all 0 for the 16 months period for ID=3?   Best regards, C ... View more

Hi,   Thank you so much for your code!   What my desired output is just exactly like Jim's result table.    I tried your code and didn't get your result table. What I got is that I can create have_A and have_B but failed to create dataset-want. It seems SAS has the same issue when I tried Jim's code. This is what SAS log provided (I am using a simpler dataset attached below):     NOTE: There were 6 observations read from the data set WORK.HAVE_A. NOTE: There were 7 observations read from the data set WORK.HAVE_B. NOTE: The data set WORK.WANT has 0 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds     Do you have any sense what is going wrong here?   Thank you very much!   Best regards, C ... View more

Hi Jim,   Thank you so much for your code. The result is exactly what I want to create.   I have tried your code and SAS reads the correct number of observation where drug='A' and drug='B', but has 0 observation for the final dataset. Following is what log provided, just so you know that I change dataset to a simpler one to have a easy way to find whether the code is working. I am attaching this dataset here below. Is it because I changed character/numeric variables?   SAS Log: NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 631:19 632:47 646:19 647:47 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 660:41 NOTE: There were 6 observations read from the data set WORK.TEST. WHERE Drug='A'; NOTE: There were 7 observations read from the data set WORK.TEST. WHERE Drug='B'; NOTE: The data set WORK.RX_PERIOD_EVALUATED has 0 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.03 seconds     Thank you so much!   Best regards,   C ... View more

Hi Guys,   Thank you for your code. I have created a dataset like this: ID Yes_or_No count_of_month month 1 1 9 2017-03 1 1 9 2017-04 1 1 9 2017-05 1 1 9 2017-06 1 1 9 2017-07 1 1 9 2017-08 1 1 9 2017-09 1 1 9 2017-10 1 1 9 2017-11 1 1 9 2017-12 2 1 6 2017-06 2 1 6 2017-07 2 1 6 2017-08 2 1 6 2017-09 2 1 6 2017-10 2 1 6 2017-11 3 0 0 . 3 0 0 . 3 0 0 . 3 0 0 . 4 0 0 .                         My next step is to (what I want): 1. Create a new variable called calendar_month with values from 2017-01 to 2018-04 (16 rows for each person) 2. Create another variable called person_permonth to represent the 'month' that the duration covered, for example, person 1 has 2017-03 in the month, then person_permonth=1 for calendar_month=2017-03; person 1 doesn't have 2017-01 and 2017-02, so person_permonth=0 for calendar_month=2017-01 and 2017-02 as follows. By doing this, every person has 16 rows with each row represent one month from 2017-01 to 2018-04. If the person is Yes_or_No=1 and has certain months in the month variable, the corresponding months under calendar_month should be 1, if the person doesn't have certain months in the variable month, the corresponding months under variable calendar_month is 0. If the person is Yes_or_No=0, the every month under calendar_month should be 0 for all 16 rows for that person. Hope I explain it clearly.   ID Yes_or_No count_of_month month calendar_month person_permonth 1 1 9   2017-01 0 1 1 9   2017-02 0 1 1 9 2017-03 2017-03 1 1 1 9 2017-04 2017-04 1 1 1 9 2017-05 2017-05 1 1 1 9 2017-06 2017-06 1 1 1 9 2017-07 2017-07 1 1 1 9 2017-08 2017-08 1 1 1 9 2017-09 2017-09 1 1 1 9 2017-10 2017-10 1 1 1 9 2017-11 2017-11 1 1 1 9 2017-12 2017-12 1 1 1 9   2018-01 0 1 1 9   2018-02 0 1 1 9   2018-03 0 1 1 9   2018-04 0 2 1 6   2017-01 0 2 1 6   2017-02 0 2 1 6   2017-03 0 2 1 6   2017-04 0 2 1 6   2017-05 0 2 1 6 2017-06 2017-06 1 2 1 6 2017-07 2017-07 1 2 1 6 2017-08 2017-08 1 2 1 6 2017-09 2017-09 1 2 1 6 2017-10 2017-10 1 2 1 6 2017-11 2017-11 1 2 1 6   2017-12 0 2 1 6   2018-01 0 2 1 6   2018-02 0 2 1 6   2018-03 0 2 1 6   2018-04 0 3 0 0 . 2017-01 0 3 0 0 . 2017-02 0 3 0 0 . 2017-03 0 3 0 0 . 2017-04 0 3 0 0 . 2017-05 0 3 0 0 . 2017-06 0 3 0 0 . 2017-07 0 3 0 0 . 2017-08 0 3 0 0 . 2017-09 0 3 0 0 . 2017-10 0 3 0 0 . 2017-11 0 3 0 0 . 2017-12 0 3 0 0 . 2018-01 0 3 0 0 . 2018-02 0 3 0 0 . 2018-03 0 3 0 0 . 2018-04 0 4 0 0 . 2017-01 0 4 0 0 . 2017-02 0 4 0 0 . 2017-03 0 4 0 0 . 2017-04 0 4 0 0 . 2017-05 0 4 0 0 . 2017-06 0 4 0 0 . 2017-07 0 4 0 0 . 2017-08 0 4 0 0 . 2017-09 0 4 0 0 . 2017-10 0 4 0 0 . 2017-11 0 4 0 0 . 2017-12 0 4 0 0 . 2018-01 0 4 0 0 . 2018-02 0 4 0 0 . 2018-03 0 4 0 0 . 2018-04 0   I know it is a little complicated. I appreciate any help with this project! Best regards, C   ... View more

Hi,   Thank you so much for the code too! One question is that what variable should I put in the var statement? What is this step supposed to do?   Best, ... View more

Hi, I just have a question that why there is d in 'yymmd7.' in the format month statement? month and b are some default functions? Thank you! Best, ... View more