As a minimal working example, I have dataset similar to the following:     DATA have ; input group_name $ fips year quarter NA; DATALINES; sam 1055 1978 1 0 sam 1055 1978 2 0 sam 1055 1978 3 0 sam 1055 1978 4 1 sam 1055 1979 1 0 sam 1055 1979 2 1 sam 1055 1979 3 1 sam 1055 1979 4 1 sam 1055 1980 1 0 sam 1055 1980 2 0 sam 1055 1980 3 0 sam 1055 1980 4 0 sam 1055 1981 1 0 sam 1055 1981 2 1 sam 1055 1981 3 1 sam 1055 1981 4 1 sam 1066 1978 1 0 sam 1066 1978 2 1 sam 1066 1978 3 0 sam 1066 1978 4 0 sam 1066 1979 1 0 sam 1066 1979 2 0 sam 1066 1979 3 0 sam 1066 1979 4 0 sam 1066 1980 1 0 sam 1066 1980 2 0 sam 1066 1980 3 0 sam 1066 1980 4 0 sam 1066 1981 1 0 sam 1066 1981 2 0 sam 1066 1981 3 0 sam 1066 1981 4 0 harry 1066 1978 1 0 harry 1066 1978 2 0 harry 1066 1978 3 0 harry 1066 1978 4 0 harry 1066 1979 1 0 harry 1066 1979 2 0 harry 1066 1979 3 0 harry 1066 1979 4 0 harry 1066 1980 1 1 harry 1066 1980 2 1 harry 1066 1980 3 1 harry 1066 1980 4 1 harry 1066 1981 1 1 harry 1066 1981 2 1 harry 1066 1981 3 1 harry 1066 1981 4 1 ; RUN; I have another dataset that lists the unique "fips":     DATA fips ; input fips; DATALINES; 1001 1044 1055 1066 ; RUN;     I want to create the following dataset:     DATA want ; input group_name $ fips year quarter NA; DATALINES; sam 1001 1978 1 0 sam 1001 1978 2 0 sam 1001 1978 3 0 sam 1001 1978 4 0 sam 1001 1979 1 0 sam 1001 1979 2 0 sam 1001 1979 3 0 sam 1001 1979 4 0 sam 1001 1980 1 0 sam 1001 1980 2 0 sam 1001 1980 3 0 sam 1001 1980 4 0 sam 1001 1981 1 0 sam 1001 1981 2 0 sam 1001 1981 3 0 sam 1001 1981 4 0 sam 1044 1978 1 0 sam 1044 1978 2 0 sam 1044 1978 3 0 sam 1044 1978 4 0 sam 1044 1979 1 0 sam 1044 1979 2 0 sam 1044 1979 3 0 sam 1044 1979 4 0 sam 1044 1980 1 0 sam 1044 1980 2 0 sam 1044 1980 3 0 sam 1044 1980 4 0 sam 1044 1981 1 0 sam 1044 1981 2 0 sam 1044 1981 3 0 sam 1044 1981 4 0 sam 1055 1978 1 0 sam 1055 1978 2 0 sam 1055 1978 3 0 sam 1055 1978 4 1 sam 1055 1979 1 0 sam 1055 1979 2 1 sam 1055 1979 3 1 sam 1055 1979 4 1 sam 1055 1980 1 0 sam 1055 1980 2 0 sam 1055 1980 3 0 sam 1055 1980 4 0 sam 1055 1981 1 0 sam 1055 1981 2 1 sam 1055 1981 3 1 sam 1055 1981 4 1 sam 1066 1978 1 0 sam 1066 1978 2 1 sam 1066 1978 3 0 sam 1066 1978 4 0 sam 1066 1979 1 0 sam 1066 1979 2 0 sam 1066 1979 3 0 sam 1066 1979 4 0 sam 1066 1980 1 0 sam 1066 1980 2 0 sam 1066 1980 3 0 sam 1066 1980 4 0 sam 1066 1981 1 0 sam 1066 1981 2 0 sam 1066 1981 3 0 sam 1066 1981 4 0 harry 1001 1978 1 0 harry 1001 1978 2 0 harry 1001 1978 3 0 harry 1001 1978 4 0 harry 1001 1979 1 0 harry 1001 1979 2 0 harry 1001 1979 3 0 harry 1001 1979 4 0 harry 1001 1980 1 0 harry 1001 1980 2 0 harry 1001 1980 3 0 harry 1001 1980 4 0 harry 1001 1981 1 0 harry 1001 1981 2 0 harry 1001 1981 3 0 harry 1001 1981 4 0 harry 1044 1978 1 0 harry 1044 1978 2 0 harry 1044 1978 3 0 harry 1044 1978 4 0 harry 1044 1979 1 0 harry 1044 1979 2 0 harry 1044 1979 3 0 harry 1044 1979 4 0 harry 1044 1980 1 0 harry 1044 1980 2 0 harry 1044 1980 3 0 harry 1044 1980 4 0 harry 1044 1981 1 0 harry 1044 1981 2 0 harry 1044 1981 3 0 harry 1044 1981 4 0 harry 1055 1978 1 0 harry 1055 1978 2 0 harry 1055 1978 3 0 harry 1055 1978 4 0 harry 1055 1979 1 0 harry 1055 1979 2 0 harry 1055 1979 3 0 harry 1055 1979 4 0 harry 1055 1980 1 0 harry 1055 1980 2 0 harry 1055 1980 3 0 harry 1055 1980 4 0 harry 1055 1981 1 0 harry 1055 1981 2 0 harry 1055 1981 3 0 harry 1055 1981 4 0 harry 1066 1978 1 0 harry 1066 1978 2 0 harry 1066 1978 3 0 harry 1066 1978 4 0 harry 1066 1979 1 0 harry 1066 1979 2 0 harry 1066 1979 3 0 harry 1066 1979 4 0 harry 1066 1980 1 1 harry 1066 1980 2 1 harry 1066 1980 3 1 harry 1066 1980 4 1 harry 1066 1981 1 1 harry 1066 1981 2 1 harry 1066 1981 3 1 harry 1066 1981 4 1 ; RUN; The idea is simple: For each group_name, if the fips does not appear in "have" but is in "fips", then fill in NA = 0 from 1978Q1 to 1981Q4. So for instance, fips=1001 and 1044 do not appear for group_name = sam, so these are filled in. Similarly, for group_name = harry, it is missing 1001, 1044, and 1055, so these are filled in.   ... View more

I have the following dataset:   DATA have; INPUT Person_ID Total_ID Treat ps_score; CARDS; 1004 1 0 0.01 1004 2 0 0.03 1004 3 1 0.043 1004 4 1 . 1004 5 1 0.02 1004 6 0 . 1007 7 0 . 1007 8 1 0.032 1007 9 1 0.77 1007 10 0 0.041 1009 11 0 0.045 1009 12 0 0.11 1009 13 0 0.82 1009 14 1 0.94 ; RUN; I want to obtain the following dataset: DATA want; INPUT Person_ID Total_ID Treat ps_score matched_ID_1 matched_ID_2 ; CARDS; 1004 1 0 0.01 . . 1004 2 0 0.03 . . 1004 3 1 0.043 10 11 1004 4 1 . . . 1004 5 1 0.02 10 . 1004 6 0 . . . 1007 7 0 . . . 1007 8 1 0.032 2 . 1007 9 1 0.77 13 . 1007 10 0 0.041 . . 1009 11 0 0.045 . . 1009 12 0 0.11 . . 1009 13 0 0.82 . . 1009 14 1 0.94 10 . ; RUN; To achieve the desired outcome, I want to do the following:   For each person_ID that has Treat = 1, I want to match it to an observation with a different person_ID with Treat = 0 that has the closest ps_score (in absolute value). Then set the value of matched_ID_1 to be equal to the Total_ID of this match. If there is a tie, then set matched_ID_2 to the tied match. If there are more than 2 ties, then continue with matched_ID_3 etc. For instance, consider the observation Total_ID = 3. It has a ps_score of 0.043. Possible matches include Total_ID = 7, 10, 11, 12, and 13. Note that Total_ID = 1, 2, and 6 are not valid because it has the same person_ID. The closest ps_score is with Total_ID = 10 and 11 (with an absolute difference of 0.002). Since this is a tie, then matched_ID_1 = 10 and matched_ID_2 = 11. Thank you for any assistance!   ... View more

I have the following dataset: DATA have; INPUT person_id item_id year amount person_name $ item_name $ document_id ; CARDS; 1001 2839 1999 3500 john steel 1305203434434 1001 3242 1999 400 john wood 1305203955327 1001 98223 1999 4 john fire 1305203820220 1001 3242 2000 5 john wood 1305204040897 1001 54233 2000 500 john bronze 1305204063236 1001 98223 2000 540 john fire 1305203399872 1001 7293 2001 230 john silver 1305203821970 1001 2839 2003 30 john steel 1305203457561 1001 3242 2003 23 john wood 1305207077153 1001 7293 2003 24 john silver 1305203683368 1001 54233 2003 55 john bronze 1305203236260 1001 98223 2003 90 john fire 1305203866126 1002 923 1987 44 sam platinum 1305204074470 1002 3232 1987 45 sam zinc 1305203358611 1002 923 2002 76 sam platinum 1305203358698 1002 3232 2002 245 sam zinc 1305203276427 1003 2839 2010 34 tom steel 1305203276403 1003 3232 2014 22 tom zinc 1305203463195 1003 923 2015 96 tom platinum 1305203714656 1003 923 2015 450 tom platinum 1301150262107 ; RUN; I want to achieve this dataset:   DATA want; INPUT person_id item_id year amount person_name $ item_name $ document_id ; CARDS; 1001 2839 1999 3500 john steel 1305203434434 1001 3242 1999 400 john wood 1305203955327 1001 7293 1999 0 john silver . 1001 54233 1999 0 john bronze . 1001 98223 1999 4 john fire 1305203820220 1001 2839 2000 0 john steel . 1001 3242 2000 5 john wood 1305204040897 1001 7293 2000 0 john silver . 1001 54233 2000 500 john bronze 1305204063236 1001 98223 2000 540 john fire 1305203399872 1001 2839 2001 0 john steel . 1001 3242 2001 0 john wood . 1001 7293 2001 230 john silver 1305203821970 1001 54233 2001 0 john bronze . 1001 98223 2001 0 john fire . 1001 2839 2003 30 john steel 1305203457561 1001 3242 2003 23 john wood 1305207077153 1001 7293 2003 24 john silver 1305203683368 1001 54233 2003 55 john bronze 1305203236260 1001 98223 2003 90 john fire 1305203866126 1002 923 1987 44 sam platinum 1305204074470 1002 3232 1987 45 sam zinc 1305203358611 1002 923 2002 76 sam platinum 1305203358698 1002 3232 2002 245 sam zinc 1305203276427 1003 923 2010 0 tom platinum . 1003 2839 2010 34 tom steel 1305203276403 1003 3232 2010 0 tom zinc . 1003 923 2014 0 tom platinum . 1003 2839 2014 0 tom steel . 1003 3232 2014 22 tom zinc 1305203463195 1003 923 2015 96 tom platinum 1305203714656 1003 923 2015 450 tom platinum 1301150262107 1003 2839 2015 0 tom steel . 1003 3232 2015 0 tom zinc . ; RUN; The newly inserted observations are given in red as in the following picture: The rule is quite simple: Fix a person_id. In a given year, if an item_id appears in some other year but does not appear in the given year, then fill in an observation for that given year with amount set to 0 while copying all other variables (person_name and item_name) but leave document_id empty.   For instance, fix person_id = 1001. If we look at year 1999, there are three item_id: 2839, 3242, and 98223. However, we can see that item_id 7293 and 54233 appears in other years but do not appear in year 1999. So we make two observations for these two item_id's in year 1999 but set amount = 0. Likewise, in year 2001, there is only one item_id = 7293, but we can see that four other item_ids (2839, 3242, 54233, 98223) appear in other years, so we make four observations for these four item_ids in year 2001 but with amount = 0. Then apply this rule across all person_id's to obtain the desired output.   I initially thought to create a list of all unique item_id's for a given person_id across all years, but then I am unsure of how to use that list to fill in observations with amount = 0. Any help would be appreciated! ... View more

I have an extract from my dataset as follows: data have; id year variable; cards; 1 1990 0.24 2 2003 0.13 2 2004 0.22 2 2005 0.26 2 2006 0.1 2 2007 0.95 5 1998 0.2 5 1999 0.33 5 2000 0.42 5 2001 0.01 5 2002 0.42 5 2004 0.54 5 2005 0.83 5 2006 0.13 5 2007 0.25 5 2011 0.98 5 2012 0.4 5 2013 0.75 5 2014 0.08 5 2015 0.09 5 2016 0.32 5 2018 0.15 5 2019 0.86 ; run; I want to calculate a three year moving average for the "variable" for each id, however, there are gaps in some years. The desired output is as follows: data have; id year variable MA ; cards; 1 1990 0.24 . 2 2003 0.13 . 2 2004 0.22 . 2 2005 0.26 0.203333333 2 2006 0.1 0.193333333 2 2007 0.95 0.436666667 5 1998 0.2 . 5 1999 0.33 . 5 2000 0.42 0.316666667 5 2001 0.01 0.253333333 5 2002 0.42 0.283333333 5 2004 0.54 . 5 2005 0.83 . 5 2006 0.13 0.5 5 2007 0.25 0.403333333 5 2011 0.98 . 5 2012 0.4 . 5 2013 0.75 0.71 5 2014 0.08 0.41 5 2015 0.09 0.306666667 5 2016 0.32 0.163333333 5 2018 0.15 . 5 2019 0.86 . ; run; where MA is the three year moving average of "variable". For example, id=1 in year 1990 would have a missing value since there is not enough lagged years to calculate a 3 year MA. For id=5, there are some "gaps", e.g., from year=2002 to year=2004, there is no data for year=2003, so year=2004 has a missing value for MA.   I know how to calculate moving averages when there are no gaps in the years, but I am not sure how to solve this. Thank you for any assistance. ... View more

Say I have a subsample of my data that looks as follows:   year event_year change id 2007 2009   3 2008 2009   3 2009 2009 1 3 2010 2009   3 2011 2009   3 2012 2009   3 2002 2004   55 2003 2004   55 2004 2004 0 55 2005 2004   55   Whenever the variable change takes a value of 1 for year = event_ye, I want to create a new column for that id only called event_year_new where it is equal to event_year+1. If change = 0, then nothing happens and event_year = event_year_new:   year event_year change id event_year_new 2007 2009   3 2010 2008 2009   3 2010 2009 2009 1 3 2010 2010 2009   3 2010 2011 2009   3 2010 2012 2009   3 2010 2002 2004   55 2004 2003 2004   55 2004 2004 2004 0 55 2004 2005 2004   55 2004   How could I do this? Of course the below code does not work because it only changes it for the case when year = event_year, but I want it to apply to each ID.   if year = event_year and change=1 then do; event_year_new = event_ye+1; end; ... View more

I have a dataset where the variables look like this:     latitude longitude latitude1 longitude1 latitude2 longitude2 latitude3 longitude3 until you reach latitude99 and longitude99.   What I wish to do is calculate the distance between latitude and longitude and each of the pairs from 1 to 99. For example:   distance1 = geodist(latitude, longitude, latitude1, longitude1) distance2 = geodist(latitude, longitude, latitude2, longitude2) distance99 = geodist(latitude, longitude, latitude99, longitude99) Obviously I do not want to manually type out each distance variable until distance99, is there a faster way to do this? Thank you.   ... View more

I am unsure of how to do something like the following. Say I have the following minimal working example table:   name fips 1991 1992 1993 1994 pol1 1001 0 0 1 1 pol2 1002 1 0 0 0 pol4 1002 0 1 1 0 pol1 1004 0 1 0 0 pol5 1004 1 1 1 1 pol6 1004 0 1 0 1 there are 6 possible names for the "name" variable ranging from pol1, pol2, ..., pol6. The variable "fips" is the ID. There are 4 years, from 1991 to 1994. I wish to obtain the following "transposed" table:   fips year pol1 pol2 pol3 pol4 pol5 pol6 1001 1991 0 0 0 0 0 0 1001 1992 0 0 0 0 0 0 1001 1993 1 0 0 0 0 0 1001 1994 1 0 0 0 0 0 1002 1991 0 1 0 0 0 0 1002 1992 0 0 0 1 0 0 1002 1993 0 0 0 1 0 0 1002 1994 0 0 0 0 0 0 1004 1991 0 0 0 0 1 0 1004 1992 1 0 0 0 1 1 1004 1993 0 0 0 0 1 0 1004 1994 0 0 0 0 1 1 The rule for the transpose is very simple: Take fips = 1001 as an example, for name = pol1, make the transpose such that the 0 and 1's correspond to the relevant year, for any names that do not correspond to the fips, set all the values to 0. In this case, in the original table, fips=1001 only corresponds to name=pol1, the renaming pol2, pol3, ..., pol6 do not appear, so all the values are 0 in the transposed table.   Thank you for any help in advance.     ... View more