Hello, Have a dataset that has an ID column, an item column and column called gap that is created to show the gap in days between the item columns per ID.   I have code where I want to create a string (like a DNA string) of the item by each ID. So for each ID I would create a column called string that would concatenate all the item column values into a long string. For example for ID 1 (in the data have below) the final string would yield O-O-O-O-O-T and for ID 2 the final string would be O-O-O-O-O-T. I know how to create the above string (see my code below). What I want to do is bring in the condition that if the gap is greater than 10 then to start the string building anew. so my output would actually look like this:   Any ideas on what to add to my code below?   What I would want is: ID string 1   O-O-O 1   O-O-T 2   O-O-O 2   O-O-T   Here is a sample of creating the have dataset.     data have; input ID 1. item $1. gap 5.; datalines; 1 O 3 1 O 4 1 O 5 1 O 15 1 O 3 1 T 4 2 O 3 2 O 5 2 O 7 2 O 11 2 O 3 2 T O ; run;     In this code I can create the string by ID, but how do I add the condition that if the gap is greater than 10 to start building a new string?   data want; set have; by ID; retain O T string; length O T $1. string $50. ; if first.ID then do; O='0'; T='0';string=' ';end; string=catx('-',string,item); if item='O' then O ='1'; else if item='T' then T='1'; run;   What I would want is: ID string 1   O-O-O 1   O-O-T 2   O-O-O 2   O-O-T ... View more

Hello!   I have a dataset where I have flagged hospital visits in to episodes (actually this article helped me a lot into making the episodes:  https://support.sas.com/resources/papers/proceedings15/3281-2015.pdf)   Now the issue I am having is that I want to get statistics, such as average, min, max, median etc. on the max eoh (i.e hospital episode).   In the dataset below patients 001 and 003 had a max of 3 episodes, patients 002, 004 and 005 had 1 hospital episode each. So the average of the 5 patients would be 1.8 episodes (3+3+1+1+1 / 5)   I having a feeling the solution is simple, like running a proc means etc. but I am getting stuck and cant figure out how to calculate the statistics.   Any suggestion would be helpful!     data testhosp1; informat patient_id $3. adm_date mmddyy10. dschg_date mmddyy10. eoh 1.; input patient_id adm_date dschg_date eoh; format adm_date dschg_date mmddyy10.; datalines; 001 04/01/2014 04/04/2014 1 001 04/02/2014 04/02/2014 1 001 04/04/2014 04/06/2014 1 001 04/07/2014 04/09/2014 1 001 09/01/2014 09/05/2014 2 001 12/01/2014 12/03/2014 3 001 12/03/2014 12/03/2014 3 001 12/03/2014 12/06/2014 3 002 04/01/2014 04/04/2014 1 003 04/02/2014 04/02/2014 1 003 04/04/2014 04/06/2014 2 003 04/07/2014 04/09/2014 2 003 09/01/2014 09/05/2014 3 004 12/01/2014 12/03/2014 1 004 12/03/2014 12/03/2014 1 005 12/03/2014 12/06/2014 1 ; run;     ... View more

  Hello, I have a question on how to consolidate a row of billing data with an admission and discharge date and to get the last row by client. I have a dataset (dataline below) where I have a client ID, an admit date and discharge date. Now the admit date and discharge dates can overlap, in some cases the admit date can be the same for a client (like client D admit date of 7/27/15) but the discharge dates are different. What I want to do is if the difference between the admit date and the previous discharge date is more than 30 then to consolidate the first admit date and the last discharge date before the difference between the two subsequent row data is more than 30 days.   Its a bit tough to explain but I hope that the code below and the following tables will help. First off the rownum variable I have is to help explain which rows I am expecting to get at the end.   DATA test; INPUT @1 rownum @5 Client $ @9 oadmit_dt mmddyy10. @21 odsch_dt mmddyy10.; FORMAT oadmit_dt mmddyy10. odsch_dt mmddyy10.; DATALINES; 1 A 10/16/2017 10/16/2017 2 B 1/28/2016 1/28/2016 3 B 1/29/2016 1/29/2016 4 B 2/24/2016 2/24/2016 5 B 2/25/2016 2/25/2016 6 B 3/3/2016 3/3/2016 7 B 3/23/2016 3/23/2016 8 B 3/24/2016 3/24/2016 9 B 4/20/2016 4/20/2016 10 B 5/4/2016 5/4/2016 11 B 5/5/2016 5/5/2016 12 B 5/12/2016 5/12/2016 13 B 5/19/2016 5/19/2016 14 B 6/16/2016 6/16/2016 15 B 6/23/2016 6/23/2016 16 B 6/30/2016 6/30/2016 17 B 8/4/2016 8/31/2016 18 B 8/11/2016 8/31/2016 19 B 8/18/2016 8/31/2016 20 B 9/1/2016 9/27/2016 21 B 9/8/2016 9/27/2016 22 B 9/14/2016 9/14/2016 23 C 1/5/2016 1/5/2016 24 C 1/5/2016 1/5/2016 25 D 7/27/2015 3/4/2016 26 D 7/27/2015 3/7/2016 27 D 7/27/2015 3/29/2016 28 D 7/27/2015 4/14/2016 29 D 7/27/2015 4/20/2016 30 D 7/27/2015 5/3/2016 31 D 7/27/2015 5/5/2016 32 D 7/27/2015 5/12/2016 33 D 7/27/2015 5/20/2016 34 D 7/27/2015 5/26/2016 35 D 7/27/2015 7/31/2018 36 D 1/5/2016 1/5/2016 37 D 2/17/2016 2/17/2016 38 D 2/22/2016 2/22/2016 39 D 2/25/2016 2/25/2016 40 D 2/29/2016 2/29/2016 41 D 8/10/2018 8/10/2018 42 D 9/12/2018 9/12/2018 43 D 9/21/2018 9/21/2018 44 D 10/2/2018 10/2/2018 45 D 10/18/2018 10/18/2018 46 D 11/5/2018 11/5/2018 47 D 11/6/2018 11/6/2018 48 D 11/13/2018 11/13/2018 49 D 11/26/2018 11/26/2018 50 D 12/17/2018 12/17/2018 ; RUN;   I run the following code, to try to calculate the gaps between the admit date of one row to the discharge date of the row before. This way if there is a gap of more than 30 days then the previous row should be output into a dataset. Furthermore I get the first admit date (i.e start_dt below) and the last discharge date (ie. end_dt in the code below) in the output dataset.     data test2; set test; by client oadmit_dt odsch_dt; retain dsch_dt2; if first.client then do; dsch_dt2=odsch_dt; gap=0; end; else do; gap=oadmit_dt-dsch_dt2; dsch_dt2=odsch_dt; end; format dsch_dt2 mmddyy10.; run; /*connect nested/continuous pieces*/ data test3; count=0; do until(last.client); set test2; by client; if gap>30 or first.client then do; if count>0 then output; count=0; start_dt=oadmit_dt; end; end_dt=odsch_dt; count+1; end; output; format start_dt end_dt mmddyy10.; run;     What I expect to get from the code above is this (note the rownum to help indicate which row I am expecting)   rownum client start date end date 1 A 10/16/2017 10/16/2017 16 B 1/28/2016 6/30/2016 22 B 8/4/2016 9/14/2016 24 C 1/5/2016 1/5/2016 34 D 7/27/2015 5/26/2016 35 D 7/27/2015 7/31/2018 36 D 1/5/2016 1/5/2016 40 D 2/17/2019 2/29/2016 41 D 8/10/2018 8/10/2018 50 D 9/12/2018 12/17/2018     But what I get is this, again note that the row numbers are not the same. Now the dates seem to be fine (expect for the 2 rows for client D), but its not grabbing the correct row. Any ideas on what I am missing in my code above?   rownum client start_dt end_dt 1 A 10/16/2017 10/16/2017 17 B 1/28/2016 6/30/2016 22 B 8/4/2016 9/14/2016 24 C 1/5/2016 1/5/2016 37 D 7/27/2015 1/5/2016 41 D 2/17/2016 2/29/2016 42 D 8/10/2018 8/10/2018 50 D 9/12/2018 12/17/2018   Thank you!   ... View more

  Hello, I have a hospital dataset and I want to be able to flag rows that are duplicates but meeting different conditions. I need to have four flags each meeting a different condition of duplicates.   The dataset that I have has the clients ID, hospital ID, date of service, a service code and a procedure code. The four flags I need are By clients 1. If the hospital ID, date of service, service code and procedure codes have duplicate rows then flag (flag1) 2. If the hospital ID is different but date of service, service code and procedure codes have duplicate rows then have another flag (flag2) 3. If the hospital ID and procedure codes are different but date of service and service code have duplicate rows then have another flag (flag3) 4. If the hospital ID and service codes are different but date of service and procedure code have duplicate rows then have another flag (flag4)   I  have the below code as a sample. NOTE the 1, 2,3 and 4 in the data set cards are examples of flags that meet the above 4 criteria. Ideally I would need four columns for each criteria and flagging 1 or 0 that meet each criteria. one would have to remove the numbers at the end to run the data step below.   data have; input client_ID $ HospitalID $ Date : yymmdd10. Service_cd procedure_cd $; format date yymmdd10.; cards;   C001 hsp001 2015/06/03 1568 ABDC C001 hsp001 2015/06/08 1400 ASDF 1 C001 hsp002 2015/06/08 1400 ASDF 1 C001 hsp002 2015/06/08 1568 SEWE C002 hsp002 2015/06/08 1568 RWRW C002 hsp002 2015/06/08 1567 EWRE C002 hsp001 2015/06/08 1567 RRRR 3 C002 hsp003 2015/06/08 1568 RRRR 3 C003 hsp001 2015/06/08 1567 RRRR C003 hsp001 2015/06/08 1567 RRRR 2 C003 hsp003 2015/06/08 1567 EWRE 2 C004 hsp001 2015/06/29 1567 RRRR 4 C004 hsp001 2015/06/29 1567 RRRR 4 C005 hsp001 2015/06/29 1567 RRRR C005 hsp001 2015/06/30 1567 RRRR C005 hsp002 2015/06/30 1567 RRRR ;   run;   Update below with what I would like to see the output as: This is how I would like to see the output. Each flag column should correspond to each point above.   Client_id Hosp_its Date service_code Procdure_code Flag1 flag2 flag3 flag4 C001 hsp001 6/3/2015 1568 ABDC 0 0 0 0 C001 hsp001 6/8/2015 1400 ASDF 1 0 0 0 C001 hsp002 6/8/2015 1400 ASDF 1 0 0 0 C001 hsp002 6/8/2015 1568 SEWE 0 0 0 0 C002 hsp002 6/8/2015 1568 RWRW 0 0 0 0 C002 hsp002 6/8/2015 1567 EWRE 0 0 1 0 C002 hsp001 6/8/2015 1567 RRRR 0 0 1 1 C002 hsp003 6/8/2015 1568 RRRR 0 0 0 1 C003 hsp001 6/8/2015 1567 RRRR 1 0 0 0 C003 hsp001 6/8/2015 1567 RRRR 1 1 0 0 C003 hsp003 6/8/2015 1567 EWRE 0 1 0 0 C004 hsp001 6/29/2015 1567 RRRR 1 0 0 0 C004 hsp001 6/29/2015 1567 RRRR 1 0 0 0 C005 hsp001 6/29/2015 1567 RRRR 0 0 0 0 C005 hsp001 6/30/2015 1567 RRRR 0 1 0 0 C005 hsp002 6/30/2015 1567 RRRR 0 1 0 0 Any assistance would be greatly appreciated!!! ... View more

Hello,   I am trying to figure out how to connect SAS to a query that is developed in MS access. There is query in Access that uses a combination of tables and other queries to create a query that I need. Now I was I found this site (https://heuristically.wordpress.com/2010/06/04/sql-sas-access-2007-accdb/) that shows me how to connect SAS to a table in MS access which worked.   But how does one connect to a query in MS access? Suggestions? links to articles that would help guide me?   Thank you in advance!         ... View more