I need to join all the variables in one dataset (caper10.caperfy10_selectvar; 17,407,272 obs) with specified variables in a 2nd dataset (temp1.fy1011_pnpts_enddate; 62,451 obs and no duplicates). I've done this with multiple tables and have never lost observations, but this time the joined dataset has 16,4999,282 obs.   My syntax is:   proc sql;     create table left_outer_join as        select *           from caper10.caperfy10_selectvar as a left join               temp1.fy1011_pnpts_enddate (keep = patssn paindate pnid enddate diag patssn1) as b              on a.sponssn = b.patssn; quit;   Both sponssn and patssn are character variables.   Any insight into why this is happening will be greatly appreciated!   I'm using SAS 9.4, 64 bit.   Thanks!   Janet ... View more

I've got a dataset with a maximum of 2 observations per ID (patid) and another variable that identifies the individual as being part of the study group (pain_id=1) or part of the comparison group (pain_id=0). Some individuals only have 1 observation and others have 2. I also have a variable with identifying the variable as the first or the second observation. I want to create a dataset containing individuals with only one observation and pain_id=1, a dataset containg only individuals with one observation and pain_id. Then I want a dataset for individuals having 2 observations.   Following is the syntax I'm using:   data pnpts nonpts twoobs;     set allpts_count;     by patid;     if (first.patid and last.patid)and pain_id=1 then output pnpts;     if (first.patid and last.patid) and pain_id=0 then output nonpts;     else output twoobs; run;   I'm able to create the 1st two datasets (pnpts and nonpts) correctly. However, the dataset twoobs contains both observations for individuals with 2 observations; but it also contains the individuals from the dataset pnpts even when they only have one observation. Twoobs does not contain the individuals from the dataset nonpts (who have only one observation).   Why am I getting the individuals from pnpts in the dataset twoobs - and more importantly, how do I prevent it?   Any help will be greatly appreciated!!   Thanks!   Janet ... View more

I have a dataset with the ID and key date for each individual. I have another dataset with the ID and multiple event dates (each is an observation) for each individual. I need to find the event date that is closest to (before) or the same as the key date for each individual. Individuals may have as few as two event dates or as many as 50 event dates.   Below are ID and key date found in one data set:   ID Key_Dt 15269 01/16/10 18658 07/31/08 23227 06/18/10 24189 10/27/08 24341 03/31/09   Here are IDs and event dates, currently residing in a second data set:   ID Evnt_Dt 15269 10/15/10 15269 10/10/10 15269 09/02/10 15269 10/12/10 15269 04/28/10 15269 08/07/10 15269 05/13/10 15269 05/31/10 18658 06/13/08 18658 06/14/08 18658 06/19/08 18658 07/05/08 18658 07/14/08 18658 08/05/08 18658 03/02/09 23227 01/28/10 23227 03/30/10 23227 04/17/10 23227 04/18/10 23227 05/10/10 23227 06/02/10 23227 06/07/10 23227 06/11/10 23227 06/26/10 23227 07/01/10 23227 07/13/10 23227 08/09/10 24189 06/04/08 24189 07/23/08 24189 08/08/08 24189 08/15/08 24189 08/19/08 24189 09/01/08 24189 12/14/08 24189 01/28/09 24341 10/25/08 24341 11/11/08 24341 01/16/09 24341 03/09/09 24671 10/14/08 24671 11/30/08 24671 12/15/08 24671 02/10/09 24671 04/02/09 24671 05/18/09 24671 06/25/09   I'm using SAS 9.3. How do I do that? Thank you! ... View more

I know this is a basic request (I'm a relatively new SAS user), but I keep getting the following error message:   ERROR: Invalid date/time/datetime constant '08/31/2010'd. ERROR: Invalid date/time/datetime constant '03/01/2011'd. ERROR: Syntax error while parsing WHERE clause.   This is my code: data caperfy11mo01_07; set caper.caperfy11mo01_12; where ('08/31/2010'd < encdate1 < '03/01/2011'd); run;   encdate1 is formatted mmddyy.10 in caper.caperfy11mo01_12; and when I look at the data, the values for encdate1 appear as I've written them in the code.   Any help is greatly appreciated. Thank you.             ... View more

I'm attempting to find individuals with at least 3 medical encounters during a 6-month period with the same specified diagnosis codes in one of the first 5 diagnosis fields (dx1-dx5). One of my colleagues had code to do the same thing but only needed 2 medical encounters; I've attempted to adapt it:   I first created a data set that included only individuals who had the desired diagnosis in one of the first 5 diagnosis fields and used that for the following:   proc sql; create table caper140106_350_obs (drop=lo hi) as select a.patssn, a.edipn, a.appttype, a.encdate1 as encdate1_check, a.nobs as nobs_check, a.dx1 as dx1_check, a.dx2 as dx2_check, a.dx3 as dx3_check, a.dx4 as dx4_check, a.dx5 as dx5_check, b.encdate1 as encdate1_plus, b.nobs as nobs_plus, b.dx1 as dx1_plus, b.dx2 as dx2_plus, b.dx3 as dx3_plus, b.dx4 as dx4_plus, b.dx5 as dx5_plus, c.encdate1, c.nobs, c.dx1, c.dx3, c.dx4, c.dx5, intnx('month',a.encdate1,-6) as lo, intnx('month',a.encdate1,6) as hi, catx('',min(a.encdate1,c.encdate1), max(a.encdate1,c.encdate1))as minmax from caper140106_350xx_sort as a, caper140106_350xx_sort as b, caper140106_350xx_sort as c where a.patssn=b.patssn=c.patssn and (b.encdate1 & c.encdate1 between calculated lo and calculated hi) and a.nobs^=b.nobs^=c.nobs order patssn, encdate1_plus; quit;   It runs, and I get results that appear to be correct, but I'm not confident that I actually have what I need. I started with 814 rows and the results gave me 79,278 rows. When I deduped the individuals, I end up with 148 people (probably reasonable).   Secondary question: I have more than 25 specfic diagnosis codes that I need to do this for. Do I need to create a data set for each of the diagnoses first and then find individuals with multiple encounters or is there a way to find folks with the multiple encounters and then pull my diagnosis codes?   I really appreciate any help with this. ... View more