Hi Y'all,   Please be patient with my explanation as my request is a tad cumbersome.   I have data that looks like this:   ID SEQNO 101 1 102 6 103 2 103 3 104 9 104 10 104 12 104 17 105 1 105 2 105 5 105 7 105 8 105 12 106 27 106 28 106 29   For every unique ID, I created the programming variable called "SEQNO". I cleaned the data, so the SEQNO values are no longer complete; however, they still go from smallest to largest value for each ID. What I need now is to do a count called NUM based on the following three conditions:   A unique ID may only have one observation, so it will only have one SEQNO value [please refer to IDs: 101, 102]. For this condition, NUM is 1. A unique ID may have multiple observations, with the SEQNO values going in numerical order [please refer to IDs: 103, 106]. For this condition, NUM is 1, regardless of the number of observations. A unique ID may have multiple observations, with the SEQNO values going in numerical order but also containing breaks in order [please refer to IDs: 104, 105]. For this condition, NUM starts as 1 but then adds 1 for every break it encounters.   (Quick note, SEQNO value does not always start at 1; however, the key point is the numerical order).   Here's one way of how the table I want may be:   ID SEQNO NUM 101 1 1 102 6 1 103 2 1 103 3 1 104 9 1 104 10 1 104 12 2 104 17 3 105 1 1 105 2 1 105 5 2 105 7 3 105 8 3 105 12 4 106 27 1 106 28 1 106 29 1   Even though in the table above, I kept every observation, I do not need every row. So an alternate table that I can use could be:   ID SEQNO NUM 101 1 1 102 6 1 103 3 1 104 17 3 105 12 4 106 29 1   (Do not worry about the SEQNO, it was just a programmatic variable meant to help me clean the data).   I am using SAS 9.4   Let me know if my request needs additional clarifications.   Muchas Gracias! ... View more

Hello Y'all,   I need some help selecting certain observations from a dataset to create a new table. For every member (ID), I want the: observations with unique dates when there are duplicate dates per member, I want the bottom (last) observation Here's how the original dataset looks like; the rows in red mark the observations I want to have in my new dataset:   ID INPBEG INPPAID 100 11/23/2008 560 100 11/23/2008 8000 100 12/5/2010 2100 101 3/9/2009 1000 101 3/20/2009 5000 101 3/30/2009 16000 101 3/30/2009 19000 101 3/30/2009 2000 102 11/6/2009 9000 102 11/6/2009 5000 103 11/5/2008 2050 103 11/5/2008 6700 104 5/17/2010 1400 104 7/9/2010 800 104 7/9/2010 4900 105 6/17/2009 3000 105 6/17/2009 750 105 11/20/2010 8500 105 12/30/2011 3600 105 12/30/2011 5000    As you can see from the red rows, there is no order/pattern of when a member will have duplicate dates. Right now my data is sorted by ID and INPBEG; that's how I know the bottom (last) observation of duplicate dates is the one I want.     I am using SAS 9.4   Thanks! ... View more

Hello everyone, I am having difficulty trying to do some calculations based on member ID, drug_ID, and drug fill_date. Please take a look at the table below. Everything in black are variables and values I currently have in my dataset. Everything in green are variables and values I want to calculate but do not know how to do it.   For every ID member, they can have one drug filled on a date or multiple drugs filled on a date. Depending whether the multiple drugs are the same drug or different drugs changes the calculations.   Guidelines for calculating "Dispense_Event" are: 1) For one drug with an amount of 30 units or less on one date, it is considered one dispense event. If the one drug has more than 30 units, then divide by 30 and round the final numerical answer down to the nearest whole integer.  2) For multiple claims for the same drug on the same date, then the drug quantities are added then divided by 30. The final numerical figure is then rounded down to the nearest whole integer. 3) For multiple drugs filled on the same date, then the calculations should adhere to the previous two guidelines (refer to patient ID "3" for reference).    ID Drug_ID RX_FILL_DT Rx_Amt Aggregate_Rx_Amt_per_DT Dispense_Event Aggreg._Disp.Events per ID 1 7 1/1/2016 30 30 1 3 1 8 1/1/2016 15 15 1 … 1 8 1/2/2016 15 30 1 … 1 8 1/2/2016 15 … … … 2 8 1/1/2016 15 30 1 1 2 8 1/1/2016 15 … … … 3 7 1/1/2016 30 60 2 4 3 7 1/1/2016 30 … … … 3 7 1/2/2016 30 30 1 … 3 8 1/3/2016 15 30 1 … 3 8 1/3/2016 15  ...  ... ... 4 7 1/3/2016 30 30 1 1 5 8 1/2/2016 15 30 1 1 5 8 1/2/2016 15 … … … 6 7 1/1/2016 30 60 2 3 6 7 1/1/2016 30 … … … 6 8 1/1/2016 15 15 1 …   In short, I need to add up all the drug quantities per drug per date for each ID member, then use the drug total unit per memeber per date to calculate the dispense_events, and then finally add up all the dispense events for each ID.    I wanted to use "RETAIN" but got bugged down by the logistics of the calculations.    I am using SAS 9.4 and would greatly appreciate your help!    Muchas Gracias ... View more

Hello Everyone,   I am struggling to recode DOB date values into something more useful. I am using PROC SQL to extract data from a larger dataset, and the large dataset has the DOB as "yyyymmdd" format (looks like this:19860222 ) and I want to convert it to date9. format (to look like this: 22FEB1986). My failed code is:   PROC SQL; CREATE TABLE NEW_DATASET AS SELECT ID, SEX, RACE, DOB INFORMAT = DATE9. FORMAT= DATE9., ENC_MNTH FROM SOURCE_DATASET WHERE 200701 <= ENC_MNTH<= 201509 ORDER BY ID, ENC_MNTH; QUIT;   Nothing happens to DOB with this code. Does someone have a fixed to the problem? If possible, please keep it in the proc sql format.    I am using SAS 9.4.    Muchas Gracias.  ... View more

Hello everyone! I need some help prioritizing some recoding on my dataset. To explain the situation, allow me to showcase my dataset and code:   Patient ID            PRINDIAG                 DIAG1   DIAG2     DIAG3   DIAG4     DIAG5         ALLDIAG                            BH 1                              11                           12           13           13           15           16           11 12 13 13 15 16                . 2                              25                           21           13           15           16           20           25 21 13 15 16 20               1 3                              13                           12           17           18           20           10           13 12 17 18 20 10               1 4                              12                           12           15           .               .               .            12 12 15                               . 5                              31                           12           17           13            .               .            31 12 17 13                          2 6                              14                           25           11           19           30                          14 25 11 19 30                     2   I got this by using the following code:   DATA &LIB..FINAL;                 SET &LIB..FINAL;                                 ARRAY ACSC{*} PRINDIAG DIAG1-DIAG5;                                 DO I=1 TO 6;                                 ALLDIAG = CATX (‘  ‘, OF ACSC{*});                                                 IF ACSC{I} IN (20, 25) THEN BH = 1;                                                 ELSE IF ACSC{I} IN (30,31) THEN BH = 2;                                 DROP I;                                 END; RUN;   I'm emphasizing row Patient "6" because that’s the one I need help with. For the recoding, I put all the diagnoses into one variable called "ALLDIAG". Based on that variable, I recode the information for variable “BH”. If you look at my program code, Patient “6” has diagnoses that includes both values for “BH”, and what I want SAS to do is to recode for the first qualified value read in “ALLDIAG”. However, what my current code does is provide the “BH” value for the last qualified value read in “ALLDIAG”. So for Patient “6”, values “25” and “30” are valid and “BH” can be recoded as “1” or “2”. “BH” does not allow for two values to be listed and so it chooses only one value. Unfortunately, what SAS does is choose the last qualified value read and recodes “BH” based on that value (in this case, “30” to recode to “2” in “BH”). What I want is for Patient “6” to have a “BH” value of “1” (for “25”) since the first diagnoses are more important.   Does anyone know how to instruct SAS to prioritize the diagnoses on the left above the ones on the right? The order matters and when there are conflicting values, the diagnosis farthest left should take priority.  To put it in another way, the diagnosis that comes first when reading left to right should take prioritiy. For Patient “6”, a diagnosis of “25” is more important than a diagnosis of “30”, so “BH” should be a “1” and not a “2”.   I am using SAS 9.2.   Thanks for the help! ... View more