SAS Support Communities

cj3 · ‎04-22-2022

@HB the first entry for ID 1 has an encounter date more than 6 months before the index date. Therefore, the two dates for this observation are out of the specified range and would not count. Does this makes sense? I currently have the following code below. Do you think this is right? proc sql; select Patient_ID, sum((intnx('month',index_date,-6,'s')<=encounter_date<=index_date) and (encounter_type='Telemedicine' or outpatient='Yes') ) as New_var from have group by Patient_ID ; quit;

cj3 · ‎04-22-2022

@Ksharp This is perfect, thank you!

cj3 · ‎04-22-2022

Hello Community, I am working with some healthcare data and I would like to create a variable that indicates the total number of healthcare encounters per each patient in my dataset according to 2 conditions: 1) encounter_type=’Telemedicine’ or outpatient=’Yes’ AND 2) intnx('month',index_date,-6,'s') <= encounter_date <= index_date; Could anyone please help me with a Proc Sql code for doing this? I’ve provided below an example of my current dataset and my desired output. Any help with this would be greatly appreciated! data have; input Prescription_date Encounter_ID Index_date Encounter_date Encounter_type outpatient; datalines; 01 123 15JUN2020 01JAN2020 . Yes 01 425 15JUN2020 02FEB2020 Telemedicine No 01 376 15JUN2020 15MAR2020 . Yes 01 974 15JUN2020 20MAY2020 . No 01 173 15JUN2020 02OCT2020 . Yes 02 298 02DEC2019 01SEP2019 Telemedicine Yes 02 245 02DEC2019 30OCT2019 . Yes 02 846 02DEC2019 01NOV2019 . Yes 02 935 02DEC2019 11NOV2019 . No 02 275 02DEC2019 02DEC2019 Telemedicine No ; Have: Patient_ID Encounter_ID Index_date Encounter_date Encounter_type outpatient 01 123 15JUN2020 01JAN2020 . Yes 01 425 15JUN2020 02FEB2020 Telemedicine No 01 376 15JUN2020 15MAR2020 . Yes 01 974 15JUN2020 20MAY2020 . No 01 173 15JUN2020 02OCT2020 . Yes 02 298 02DEC2019 01SEP2019 Telemedicine Yes 02 245 02DEC2019 30OCT2019 . Yes 02 846 02DEC2019 01NOV2019 . Yes 02 935 02DEC2019 11NOV2019 . No 02 275 02DEC2019 02DEC2019 Telemedicine No Want: Patient_ID New_var 01 2 02 4

cj3 · ‎04-13-2022

@Kurt_Bremser This is perfect. Thank you!!

cj3 · ‎04-12-2022

Hello Community, I need to create a variable in my dataset to indicate whether or not a patient received a second prescription of a medication within 34 days of their initial prescription (i.e., the patient has one or more unique prescription dates within 34 days of the index prescription date). Please see below for how my dataset is currently organized and how I would like the output to be like. Note that my data is already sorted ascending by prescription date, therefore the first date listed for each patient is the index date (i.e., the first prescription received). Also note that sometimes multiple rows of data are entered for the same prescription, which can result in the same prescription date shown more than once for a patient. Therefore, it is important that the code identifies whether a UNIQUE date is within 34 days of the index date. Finally, please note that patients who did not receive the medication will have the prescription_date variable equal to missing. In these cases, it should be new_var=0 since the condition would be false for these patients as well. Any help with the coding for doing this would be greatly appreciated!! Have: Patient_id Prescription_date 001 17JUN2020 001 23JUL2020 001 31JUL2020 002 15FEB2020 002 15FEB2020 002 30MAR2020 003 02JAN2021 003 02JAN2021 004 21APR2021 004 20JUN2021 004 01JUL2021 005 19OCT2021 005 25OCT2021 005 25OCT2021 005 28SEP2021 006 . Want: Patient_id New_var 001 1 002 0 003 0 004 0 005 1 006 0

cj3 · ‎03-24-2020

@mkeintz Great, thank you! I tested the code and it works except that a missing value is showing up for drugb_use at Visit 1 for subject 1. See below: Getting: PID VISITNO Date Druga_use Drugb_use 01 1 03/30/2018 1 . 01 2 05/02/2018 0 0 01 3 05/30/2018 0 0 02 1 04/09/2018 1 1 02 2 05/09/2018 1 1 02 3 . . . Instead, drugb_use at Visit 1 for subject 1 should be 0: Need: PID VISITNO Date Druga_use Drugb_use 01 1 03/30/2018 1 0 01 2 05/02/2018 0 0 01 3 05/30/2018 0 0 02 1 04/09/2018 1 1 02 2 05/09/2018 1 1 02 3 . . . Can you please let me know how the code should be modified to fix this? I have provided the datasets below for your reference. data have1; input pid d1date d2date d3date d4date d5date d6date d7date d1usea d2usea d3usea d4usea d5usea d6usea d7usea d1useb d2useb d3useb d4useb d5useb d6useb d7useb; informat d1date d2date d3date d4date d5date d6date d7date MMDDYY8.; format d1date d2date d3date d4date d5date d6date d7date MMDDYY8.; informat d1usea d2usea d3usea d4usea d5usea d6usea d7usea $1.; format d1usea d2usea d3usea d4usea d5usea d6usea d7usea $X35979X.; informat d1useb d2useb d3useb d4useb d5useb d6useb d7useb 3.; format d1useb d2useb d3useb d4useb d5useb d6useb d7useb 3.; datalines; 01 . . . 02/28/18 03/01/18 03/02/18 03/03/18 . . . 0 0 1 0 . . . . . 0 . 01 03/04/18 03/05/18 03/06/18 03/07/18 03/08/18 03/09/18 03/10/18 0 0 0 0 0 1 0 . . . . . 0 . 01 03/11/18 03/12/18 03/13/18 03/14/18 03/15/18 03/16/18 03/17/18 0 1 0 0 0 0 0 . 0 . . . . . 01 03/18/18 03/19/18 03/20/18 03/21/18 03/22/18 03/23/18 03/24/18 0 0 0 0 0 0 0 . . . . . . . 01 03/25/18 03/26/18 03/27/18 03/28/18 03/29/18 03/30/18 03/31/18 0 0 1 0 0 0 0 . . 0 . . . . 01 04/01/18 04/02/18 04/03/18 04/04/18 04/05/18 04/06/18 04/07/18 0 0 0 0 0 0 0 . . . . . . . 01 04/08/18 04/09/18 04/10/18 04/11/18 04/12/18 04/13/18 04/14/18 0 0 0 0 0 0 0 . . . . . . . 01 04/15/18 04/16/18 04/17/18 04/18/18 04/19/18 04/20/18 04/21/18 0 0 0 0 0 0 0 . . . . . . . 01 04/22/18 04/23/18 04/24/18 04/25/18 04/26/18 04/27/18 04/28/18 0 0 0 0 0 0 0 . . . . . . . 01 04/29/18 04/30/18 05/01/18 05/02/18 05/03/18 05/04/18 05/05/18 0 0 0 0 0 0 0 . . . . . . . 01 05/06/18 05/07/18 05/08/18 05/09/18 05/10/18 05/11/18 05/12/18 0 0 0 0 0 0 0 . . . . . . . 01 05/13/18 05/14/18 05/15/18 05/16/18 05/17/18 05/18/18 05/19/18 0 0 0 0 0 0 0 . . . . . . . 01 05/20/18 05/21/18 05/22/18 05/23/18 05/24/18 05/25/18 05/26/18 0 0 0 0 0 0 0 . . . . . . . 01 05/27/18 05/28/18 05/29/18 . . . . 0 0 0 . . . . . . . . . . . 02 . . . . . . 03/10/18 . . . . . . 0 . . . . . . . 02 03/11/18 03/12/18 03/13/18 03/14/18 03/15/18 03/16/18 03/17/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 03/18/18 03/19/18 03/20/18 03/21/18 03/22/18 03/23/18 03/24/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 03/25/18 03/26/18 03/27/18 03/28/18 03/29/18 03/30/18 03/31/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/01/18 04/02/18 04/03/18 04/04/18 04/05/18 04/06/18 04/07/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/08/18 04/09/18 04/10/18 04/11/18 04/12/18 04/13/18 04/14/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/15/18 04/16/18 04/17/18 04/18/18 04/19/18 04/20/18 04/21/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/22/18 04/23/18 04/24/18 04/25/18 04/26/18 04/27/18 04/28/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/29/18 04/30/18 05/01/18 05/02/18 05/03/18 05/04/18 05/05/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 05/06/18 05/07/18 05/08/18 05/09/18 . . . 1 1 1 1 . . . 1 1 1 1 . . . ; data have2; input pid Screen_Date: mmddyy10. VisitNo Visit_date: mmddyy10.; format screen_date visit_date mmddyy10.; datalines; 01 03/30/2018 2 05/02/2018 01 03/30/2018 3 05/30/2018 02 04/09/2018 2 05/09/2018 02 04/09/2018 3 . ;

cj3 · ‎03-23-2020

@mkeintz also to follow up regarding adding additional drug use variables, my data set has it where a non-missing value (1/0) for the additional drug use variable (e.g., drugb_use) is only present for a date if there is a "1" for druga_use for that same date. In other words, "drugb_use" would not be assessed if there was no "druga_use" for a particular date and therefore would show as missing. However, my desired output would indicate no "drugb_use" if there was also no "druga_use", as opposed to it showing as missing. Of course, if the visit date is missing, then druga_use and drubb_use would also be missing. I have provided my example dataset again below with this added as well as the desired dataset. Any suggestions on incorporating this to the earlier code would be greatly appreciated. Want: PID VISITNO Date Drug A Use Drug B Use 01 1 03/30/2018 1 0 01 2 05/02/2018 0 0 01 3 05/30/2018 0 0 02 1 04/09/2018 1 1 02 2 05/09/2018 1 1 02 3 . . . ….. Have: Subject ID D1 date D2 date D3 date D4 date D5 date D6 date D7 date D1 drug A use D2 drug A use D3 drug A use D4 drug A use D5 drug A use D6 drug A use D7 drug A use D1 drug B use D2 drug B use D3 drug B use D4 drug B use D5 drug B use D6 drug B use D7 drug B use 01 . . . 02/28/18 03/01/18 03/02/18 03/03/18 . . . 0 0 1 0 . . . . . 0 . 01 03/04/18 03/05/18 03/06/18 03/07/18 03/08/18 03/09/18 03/10/18 0 0 0 0 0 1 0 . . . . . 0 . 01 03/11/18 03/12/18 03/13/18 03/14/18 03/15/18 03/16/18 03/17/18 0 1 0 0 0 0 0 . 0 . . . . . 01 03/18/18 03/19/18 03/20/18 03/21/18 03/22/18 03/23/18 03/24/18 0 0 0 0 0 0 0 . . . . . . . 01 03/25/18 03/26/18 03/27/18 03/28/18 03/29/18 03/30/18 03/31/18 0 0 1 0 0 0 0 . . 0 . . . . 01 04/01/18 04/02/18 04/03/18 04/04/18 04/05/18 04/06/18 04/07/18 0 0 0 0 0 0 0 . . . . . . . 01 04/08/18 04/09/18 04/10/18 04/11/18 04/12/18 04/13/18 04/14/18 0 0 0 0 0 0 0 . . . . . . . 01 04/15/18 04/16/18 04/17/18 04/18/18 04/19/18 04/20/18 04/21/18 0 0 0 0 0 0 0 . . . . . . . 01 04/22/18 04/23/18 04/24/18 04/25/18 04/26/18 04/27/18 04/28/18 0 0 0 0 0 0 0 . . . . . . . 01 04/29/18 04/30/18 05/01/18 05/02/18 05/03/18 05/04/18 05/05/18 0 0 0 0 0 0 0 . . . . . . . 01 05/06/18 05/07/18 05/08/18 05/09/18 05/10/18 05/11/18 05/12/18 0 0 0 0 0 0 0 . . . . . . . 01 05/13/18 05/14/18 05/15/18 05/16/18 05/17/18 05/18/18 05/19/18 0 0 0 0 0 0 0 . . . . . . . 01 05/20/18 05/21/18 05/22/18 05/23/18 05/24/18 05/25/18 05/26/18 0 0 0 0 0 0 0 . . . . . . . 01 05/27/18 05/28/18 05/29/18 . . . . 0 0 0 . . . . . . . . . . . 02 . . . . . . 03/10/18 . . . . . . 0 . . . . . . . 02 03/11/18 03/12/18 03/13/18 03/14/18 03/15/18 03/16/18 03/17/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 03/18/18 03/19/18 03/20/18 03/21/18 03/22/18 03/23/18 03/24/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 03/25/18 03/26/18 03/27/18 03/28/18 03/29/18 03/30/18 03/31/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/01/18 04/02/18 04/03/18 04/04/18 04/05/18 04/06/18 04/07/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/08/18 04/09/18 04/10/18 04/11/18 04/12/18 04/13/18 04/14/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/15/18 04/16/18 04/17/18 04/18/18 04/19/18 04/20/18 04/21/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/22/18 04/23/18 04/24/18 04/25/18 04/26/18 04/27/18 04/28/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 04/29/18 04/30/18 05/01/18 05/02/18 05/03/18 05/04/18 05/05/18 1 1 1 0 1 1 0 1 1 1 . 1 1 . 02 05/06/18 05/07/18 05/08/18 05/09/18 . . . 1 1 1 1 . . . 1 1 1 1 . . . …..

cj3 · ‎03-23-2020

Hi @mkeintz, Thank you so much for your time, this worked perfectly! I am still very much a SAS newbie, so I was wondering if you could also provide some feedback on how to slightly modify the above code to further suit my aims. 1. First, could you please show me how the above code could be modified to efficiently examine use of additional drugs, such as adding "drugb_use", "drugc_use" variables in addition to "druga_use"? 2. Also, it appears that the code you provided put the dates in BEST12. format, although my input datasets have the dates in MMDDYY8. format. Any suggestions on how to get the date format back to MMDDYY8. would be appreciated as this format just seems to be a little more readable. Thank you!!

cj3 · ‎03-22-2020

Hello Community, I am working on a data analysis problem for which I would greatly appreciate some programming help. Basically, I need to know whether or not subjects in my dataset reported drug use (Drug A) during a research study at specific time points. Specifically, I would like to know whether subjects reported use of Drug A during each of the following time periods during the study: Prior to the study Screening date (dates<Screen_Date) Between the Screening date and Visit 2 (Screen_Date<=dates<Visit 2) Between Visit 2 and Visit 3 (Visit 2<=dates<Visit 3) Please see an example of the desired output data set (Want) below. The VISITNO corresponds to the number of each of the 3 study visits where Visit 1 would correspond to the Screening date. Want: PID VISITNO Date Drug A Use 01 1 03/30/2018 1 01 2 05/02/2018 0 01 3 05/30/2018 0 02 1 04/09/2018 1 02 2 05/09/2018 1 02 3 . . ….. I have also provided an example of the two data sets I have to produce this output. The first is a dataset listing the visit numbers and visit dates for each subject (Have 1). Note that the Screening Visit is a separate variable; however, I would like to have a single variable in my output dataset that designates the Screening Visit as Visit 1 (See Want above). Please note that some visits may not have attended a study visit, hence, the missing data for a visit date (subject 02 in the example below). Have 1: Subject ID Screen_Date VisitNo Visit_date 01 03/30/2018 2 05/02/2018 01 03/30/2018 3 05/30/2018 02 04/09/2018 2 05/09/2018 02 04/09/2018 3 . The second dataset that I have indicates the dates that drug use occurred on (Have 2). That is, Drug A use is indicated by a "1" in the "D1....D7 drug A use" columns. Each row of data for each participant represents an entire week. The "D1 drug use" column indicates drug use for the "D1 date" column, the "D2 drug use" column indicates drug use for the "D2 date" column, and so on. Please also note that some participants may not have completed a study visit. Therefore, the drug use for that visit would be missing (e.g., Subject ID 02 in the example below). Have 2: Subject ID D1 date D2 date D3 date D4 date D5 date D6 date D7 date D1 drug A use D2 drug A use D3 drug A use D4 drug A use D5 drug A use D6 drug A use D7 drug A use 01 . . . 02/28/18 03/01/18 03/02/18 03/03/18 . . . 0 0 1 0 01 03/04/18 03/05/18 03/06/18 03/07/18 03/08/18 03/09/18 03/10/18 0 0 0 0 0 1 0 01 03/11/18 03/12/18 03/13/18 03/14/18 03/15/18 03/16/18 03/17/18 0 1 0 0 0 0 0 01 03/18/18 03/19/18 03/20/18 03/21/18 03/22/18 03/23/18 03/24/18 0 0 0 0 0 0 0 01 03/25/18 03/26/18 03/27/18 03/28/18 03/29/18 03/30/18 03/31/18 0 0 1 0 0 0 0 01 04/01/18 04/02/18 04/03/18 04/04/18 04/05/18 04/06/18 04/07/18 0 0 0 0 0 0 0 01 04/08/18 04/09/18 04/10/18 04/11/18 04/12/18 04/13/18 04/14/18 0 0 0 0 0 0 0 01 04/15/18 04/16/18 04/17/18 04/18/18 04/19/18 04/20/18 04/21/18 0 0 0 0 0 0 0 01 04/22/18 04/23/18 04/24/18 04/25/18 04/26/18 04/27/18 04/28/18 0 0 0 0 0 0 0 01 04/29/18 04/30/18 05/01/18 05/02/18 05/03/18 05/04/18 05/05/18 0 0 0 0 0 0 0 01 05/06/18 05/07/18 05/08/18 05/09/18 05/10/18 05/11/18 05/12/18 0 0 0 0 0 0 0 01 05/13/18 05/14/18 05/15/18 05/16/18 05/17/18 05/18/18 05/19/18 0 0 0 0 0 0 0 01 05/20/18 05/21/18 05/22/18 05/23/18 05/24/18 05/25/18 05/26/18 0 0 0 0 0 0 0 01 05/27/18 05/28/18 05/29/18 . . . . 0 0 0 . . . . 02 . . . . . . 03/10/18 . . . . . . 0 02 03/11/18 03/12/18 03/13/18 03/14/18 03/15/18 03/16/18 03/17/18 1 1 1 0 1 1 0 02 03/18/18 03/19/18 03/20/18 03/21/18 03/22/18 03/23/18 03/24/18 1 1 1 0 1 1 0 02 03/25/18 03/26/18 03/27/18 03/28/18 03/29/18 03/30/18 03/31/18 1 1 1 0 1 1 0 02 04/01/18 04/02/18 04/03/18 04/04/18 04/05/18 04/06/18 04/07/18 1 1 1 0 1 1 0 02 04/08/18 04/09/18 04/10/18 04/11/18 04/12/18 04/13/18 04/14/18 1 1 1 0 1 1 0 02 04/15/18 04/16/18 04/17/18 04/18/18 04/19/18 04/20/18 04/21/18 1 1 1 0 1 1 0 02 04/22/18 04/23/18 04/24/18 04/25/18 04/26/18 04/27/18 04/28/18 1 1 1 0 1 1 0 02 04/29/18 04/30/18 05/01/18 05/02/18 05/03/18 05/04/18 05/05/18 1 1 1 0 1 1 0 02 05/06/18 05/07/18 05/08/18 05/09/18 . . . 1 1 1 1 . . . ….. Note: The data includes 30 days prior to each subject’s Screening Date; hence, the missing data for some cells.

cj3 · ‎03-07-2020

Hi @PaigeMiller Yes, the variable name "ID" indicates the subject. Baseline drug use is defined by any ( Yes vs. No) drug before the randomization date. Drug use is indicated by a "1" in the "D1....D7 drug use" columns. Each row of data for each participant represents an entire week. The "D1 drug use" column indicates drug use for the "D1 date" column, the "D2 drug use" column indicates drug use for the "D2 date" column, and so on. To know whether drug use occurred before the randomization date, you then would check each date prior to the the date listed in for the "Randomization date" variable. In this case, the randomization date is 04/02/18. Therefore, I need to determine drug use on 04/01/18 and before. In this example, drug use occurred on 03/02/18, 03/09/18, 03/12/18, 03/28/18, and 03/30/18. So, the baseline drug use variable would be "1". Please let me know if you have any other questions. Thank you for your help!

cj3 · ‎03-06-2020

Hello Community, I am working on a data analysis problem for which I would greatly appreciate some programming help. Basically, I need to create a variable for each subject in my dataset that indicates whether (yes/no) they reported drug use prior to the study randomization date (i.e., baseline drug use). Below is what my dataset looks like as well as the desired output. Any feedback would be much appreciated! Please let me know if any additional information or clarification would be helpful. Have: ID Randomization date Week start date D1 date D2 date D3 date D4 date D5 date D6 date D7 date D1 drug use D2 drug use D3 drug use D4 drug use D5 drug use D6 drug use D7 drug use 01 04/02/18 02/25/18 . . . 02/28/18 03/01/18 03/02/18 03/03/18 . . . 0 0 1 0 01 04/02/18 03/04/18 03/04/18 03/05/18 03/06/18 03/07/18 03/08/18 03/09/18 03/10/18 0 0 0 0 0 1 0 01 04/02/18 03/11/18 03/11/18 03/12/18 03/13/18 03/14/18 03/15/18 03/16/18 03/17/18 0 1 0 0 0 0 0 01 04/02/18 03/18/18 03/18/18 03/19/18 03/20/18 03/21/18 03/22/18 03/23/18 03/24/18 0 0 0 0 0 0 0 01 04/02/18 03/25/18 03/25/18 03/26/18 03/27/18 03/28/18 03/29/18 03/30/18 03/31/18 0 0 1 0 0 1 0 01 04/02/18 04/01/18 04/01/18 04/02/18 04/03/18 04/04/18 04/05/18 04/06/18 04/07/18 0 0 0 0 0 0 0 01 04/02/18 04/08/18 04/08/18 04/09/18 04/10/18 04/11/18 04/12/18 04/13/18 04/14/18 0 0 0 0 0 0 0 … Note: The data includes 30 days prior to each subject’s randomization date; hence, the missing data for some cells. My dataset also includes 6 months of data after the randomization date; however, these data are irrelevant to my analysis question RE: baseline drug use. Want: ID Baseline drug use 01 1 02 0 03 1 … …

Online Status	Offline
Date Last Visited	‎04-27-2022 12:57 AM

SAS Support Communities

Re: Help with PROC SQL conditional counting

Re: Help with PROC SQL conditional count

Help with PROC SQL conditional count

Re: Subsequent date-interval computation

Subsequent date-interval computation

Re: Calendar data analysis

Re: Calendar data analysis

Re: Calendar data analysis

Calendar data analysis

Re: Calendar data analysis

Re: Help with PROC SQL conditional count

Re: Subsequent date-interval computation

Re: Calendar data analysis

Re: Calendar data analysis

Re: Calendar data analysis

Re: Help with PROC SQL conditional counting

Re: Help with PROC SQL conditional count

Help with PROC SQL conditional count

Re: Subsequent date-interval computation

Subsequent date-interval computation

Re: Calendar data analysis

Re: Calendar data analysis

Re: Calendar data analysis

Calendar data analysis

Re: Calendar data analysis

Calendar data analysis

Follow Us

What is...