Hi all, quick question about populating missing values by group, but doing so systematically so that the pattern of data for the 'cases' is applied to the 'controls'. I've included a Have/Want example. The only thing different between this sample and what i want is that there are multiple controls per case (e.g., 6:1), but they are sorted so that once a new case occurs the pattern will repeat itself following the same logic. Thank you for any advice to help me figure it out. I think it needs a do loop in order to get the same pattern, but so far what i've been able to get is not correct. Nate DATA Have; INPUT ID $ Case Set Time ; CARDS; A 1 1 -3 A 1 1 -2 A 1 1 -1 A 1 1 0 A 1 1 1 A 1 1 2 A 1 1 3 B 0 1 . B 0 1 . B 0 1 . B 0 1 . B 0 1 . B 0 1 . B 0 1 . C 1 2 -2 C 1 2 -1 C 1 2 0 C 1 2 1 C 1 2 2 C 1 2 3 C 1 2 4 D 0 2 . D 0 2 . D 0 2 . D 0 2 . D 0 2 . D 0 2 . D 0 2 . ;   DATA Want; INPUT ID $ Case Set Time ; CARDS; A 1 1 -3 A 1 1 -2 A 1 1 -1 A 1 1 0 A 1 1 1 A 1 1 2 A 1 1 3 B 0 1 -3 B 0 1 -2 B 0 1 -1 B 0 1 0 B 0 1 1 B 0 1 2 B 0 1 3 C 1 2 -2 C 1 2 -1 C 1 2 0 C 1 2 1 C 1 2 2 C 1 2 3 C 1 2 4 D 0 2 -2 D 0 2 -1 D 0 2 0 D 0 2 1 D 0 2 2 D 0 2 3 D 0 2 4 ;   ... View more

Hi everyone, I'm having difficulty coming up with the correct logic coding to set a conditional if/else statement that is referencing a range of values as its reference. I'll provide a dataline with desired output below. The field district is my grouping value.   By district, I would like the following 3 unique values to populate the want column  1. If Have = 99 for all five years then Want = 1  2. If Have = . before it equaled 99, then Want = 2 ; 3. If Have = . after Have = 99, then Want = 3 ;   It's the pre/post logic that i'm struggling with as there are instances where the event occurred after the start date, but did not continued until the end date. Thanks everyone! I appreciate the help, nate   data Example; infile datalines ; input district year have want; datalines; 1 2006 99 1 1 2007 99 1 1 2008 99 1 1 2009 99 1 1 2010 99 1 2 2006 . 2 2 2007 . 2 2 2008 99 1 2 2009 99 1 2 2010 . 3 3 2006 99 1 3 2007 99 1 3 2008 99 1 3 2009 99 1 3 2010 . 3 4 2006 . 2 4 2007 . 2 4 2008 99 1 4 2009 99 1 4 2010 99 1 ; ... View more

Hi everyone,  I'm having difficult building a solution to a problem I'm working on where I assign a value to a recipient based on multiple conditions. This can be done in a data step or SQL.    Scenario: I'm working with a dataset that Has an ID variable, a claimtype (e.g., emergency, outpatient), the type of provider the person saw - and the important variable - was the provider they saw the one that was assigned to them. In all instances, the recipients is going to have an ED encounter, and I need to keep this variable. What i want to do is group recipients into three groups (A) always saw assigned provider, (B) sometimes saw assigned provider, and (C) never saw assigned provider. Ultimately these new variables will be my grouping variables to run some frequency statistics on (with dependent variable being ED visit) .   I've created a Want and Have dataset below. Any help on this is appreciated. I'm not too worried about the blanks that I have in the Want table as I plan to just populate the empty spaces using a Retain function, but if you know of an easier way to flag a recipient, by ID, into one of the 3 groups then that's also great!   thank you for your help, Nate   data Have; input ID $ Claim $ provtype $ assigned &; datalines ; 1 ED EM . 1 OP SPEC . 1 OP SPEC . 1 OP MD 1 1 OP MD 0 2 ED EM . 2 OP MD 1 2 OP MD 1 3 ED EM . 3 OP SPEC . 3 OP MD 1 3 OP MD 1 3 OP MD 1 3 OP MD 1 3 OP MD 1 4 ED EM . 4 OP MD 0 4 OP SPEC . 4 OP MD 0 ; ; run ; data want; input ID $ Claim $ provtype $ assigned & both $ always $ never $; datalines ; 1 ED EM . . . . 1 OP SPEC . . . . 1 OP SPEC . . . . 1 OP MD 1 1 . . 1 OP MD 0 1 . . 2 ED EM . . . . 2 OP MD 1 . 1 . 2 OP MD 1 . 1 . 3 ED EM . . . . 3 OP SPEC . . . . 3 OP MD 1 1 . . 3 OP MD 1 1 . . 3 OP MD 1 1 . . 3 OP MD 1 1 . . 3 OP MD 1 1 . . 4 ED EM . . . . 4 OP MD 0 . . 1 4 OP SPEC . . . . 4 OP MD 0 . . 1 ; ... View more

Hi all, I have a many-to-many join issue arising using sql trying to join two tables that have a different number of records and both have repeating subjects. My need: combine all three tables without multiplying them.   Scenario: Table 1 is demographics table and this is a unique list without repeating subjects. Table 2 is an event table that is allowed to have repeating events and they are organized by calendar date. Table 3 is another event table and it is also organized by calendar date. In trying to do left inner joins what happens is that if I have 10 events in Table 2 and 20 events in Table 3, my sql script is generating 20 total events. I believe this is an issue with not using the correct placement of 'select distinct', but I haven't been able my script into the right format. Below is some sample tables that I constructed as well as the desired output - any help would be appreciated. thank you and stay healthy.    Sample script:   data Have1; input ID $ age1 $ ; datalines ; a 20 b 28 c 23 d 31 ; data Have2; input ID $ Date1 mmddyy10. Type1 $ ; format date1 mmddyy8.; datalines; A 08/01/2009 OP A 08/09/2009 OP A 08/27/2009 OP A 08/31/2009 OP B 09/15/2009 OP B 09/16/2009 OP C 10/02/2009 OP D 10/15/2009 OP D 10/24/2009 OP D 10/31/2009 OP ; data Have3 ; input ID $ Date2 mmddyy10. type2 $; format date2 mmddyy8.; datalines ; A 08/05/2009 DH B 09/03/2009 DH B 09/16/2009 DH B 09/24/2009 DH C 10/24/2009 DH C 10/31/2009 DH D 11/02/2009 DH ;   Want (sample below):  A Table that has 16 total events (there were 10 type 1 events and 7 type 2 events, but two events occurred on same day so would be listed together).    ID Age Date1 Type1 Date2 Type2 A 20 8/1/09 OP . . A 20 . . 8/5/09 DH A 20 8/9/09 OP . . A 20 8/27/09 OP . . A 20 8/31/09 OP . . B 28 . . 9/3/09 . B 28 9/15/09 OP . . B 28 9/16/09 OP 9/16/09 DH B 28 . . 9/24/09 DH C 23 10/2/09 OP . . C 23 . . 10/24/09 DH C 23 . . 10/31/09 DH D 31 10/15/09 OP . . D 31 10/24/09 OP . . D 31 10/31/09 OP . . D 31 . . 11/2/09 DH ... View more