Hi,   I have data at the person-month level, so each row=1 person in one month. It has ID, month/year, and two categorical variables. What I want is to collapse to the person-year level as follows:   1. I want the value of categ_var1 as of the earliest month that each person has a value (not missing) for categ_var1.    2. If the person ever has categ_var2='abc', I want an indicator saying that they had categ_var2='abc' in that year.   The data are like this: ID   mth_year  categ_var1   categ_var2 1     201301            ' '                abc 1     201302            ' '                ghi 1     201303           def              ghi 2     201301           xyz              jkl     2     201302           def              jkl   So the yearly dataset would be like this: ID   first_categ_var1  categ_var2_abc_ind 1           def                              1 2           xyz                              0   Any help is much appreciated.       ... View more

Hi,   I have a dataset which is supposed to be at the person-level, but really there are some people on multiple rows (I know because I've added an umbrella ID and there are some people on two rows with the same umbrella ID but different values of my former ID variable, ID2). My dataset has ID1 (the umbrella ID), ID2, indicators for each month (=1 if the person had an event in that month), and a number of categorical variables.    ID1   ID2    mth_200901      mth_200902...mth_201311 mth_201312   categ_1          categ_2 1        1             1                          1                  .                       .               abc                    pqr 1        2             .                            .                  1                      1              def                     xyz   What I want is two things. One is simple and is just that for each value of ID1, they should have a value of 1 for each mth_ indicator as long as one of their sub-IDs (ID2) had a 1 for that month. The other is that I want the values of categ_1 and categ_2 (and the several other categorical vars) as of the row with the latest month that =1. So my final dataset would have this summary row for ID1=1.   ID1   mth_200901      mth_200902...mth_201311 mth_201312   categ_1          categ_2 1               1                          1                 1                       1              def                   xyz   Any help is much appreciated.   ... View more

Hi,   I have data at the person-month level, that is, each row is one person in one month. There are dupes. The variables I have are ID, month/year, and two categorical vars.    ID         mth_year     categ_1     categ_2 1           200901            abc          xyz 1           200901            abc          pqr 1           200902            abc          pqr 2           201001            def           xyz 2           201001            ghi           xyz 2           201002            ghi           xyz 3           200907            abc          pqr 3           200908            abc          pqr   What I want is to do a data check: I want to know how many people have duplicate rows (as in, same ID and mth_year--not same categ_1 and categ_2) in their earliest mth_year. Then within that group, I want to know how many of them have different values of 1. categ_1 and 2. categ_2 across their dupe rows.   Any help is much appreciated.  ... View more

Hi,   I have a SAS dataset which I am trying to convert to an xls file using SE Guide 4.3 and the following code:   proc export data=data1 dbms=xls outfile="filepath\excel_file.xls" replace; run;   According to the log, the code runs fine with no issues whatsoever. However, when I open the Excel file, I get an error message that the file was corrupted and needs to be repaired by Excel so it can be readable. When I repair it, there are no variable names/headers in the file, and it looks like only the numeric vars are filled in--the character variables seem to be missing altogether. I do have a number of long-ish character variables, but not extremely so--the longest is 100 characters.    Any help is much appreciated.  ... View more

Hi,   I have a dataset at the person level that has ID and yearly values of a secondary ID that link to Dataset 2:   Dataset 1: ID     Dataset2_ID_2009     Dataset2_ID_2010      Dataset2_ID_2011 1            123                                  123                            234 2            234                                                                    456   As you can see, the secondary ID can be missing in a given year, and can either change or stay the same from year to year.    Dataset 2 has the Dataset2 IDs in rows with a start and end date, as well as two categorical vars that are true as of the start and end dates:   Dataset 2:   Dataset_2_ID       Start_dt         End_dt              Categ_var1          Categ_var2 123                       1/3/2000       10/02/2012             abc                    def 123                       2/5/2013       12/01/2013             ghi                     jkl 234                       7/1/2001       9/3/2012                 abc                    def 456                       9/3/2001       4/5/2003                 ghi                     jkl 456                      8/7/2010       5/17/2014                mno                  pqr   What I want is to keep the form of Dataset 1 but pull in the value of Categ_var1 and Categ_var2 as of January 1st of the year of Dataset_2_ID (for all years), like this:   Final dataset: ID     Dataset2_ID_2009   Categ_var_1_2009  Categ_var_2_2009    Dataset2_ID_2010   Categ_var_1_2010  Categ_var_2_2010    1            123                                  abc                        def                            123                              abc                           def (first row continued) Dataset2_ID_2011    Categ_var_1_2011         Categ_var_2_2011 234                                     abc                                    def   I know how to do this the long way by splitting Dataset 1 into yearly cohorts and doing a join for each year, but I'm hoping that there is a way to do it with fewer steps.    Any help is much appreciated.    ... View more

Hi,   I have a dataset that is at the level of person and values of a categorical variable (categ_1). There are several possible values of categ_1 but no person can have more than 2 values. Each row also has a value for categ_2 and a date variable.    ID   categ_1     categ_2     date 1      abc            jkl             1/1/2009 1      def            mno          1/1/2010 2      abc           mno          1/1/2009   What I'm trying to do is isolate those people with multiple values of categ_1, and find out what their values of categ_2 are for each value of categ_1, by date (so the one with the earlier date is their first categ_1 and the one with the later date is their second categ_1).    What I did was this:   proc sort data=want; by ID date; run;   data have; set want; by ID date; if first.ID then first_categ_1=1; else second_categ_1=1; if first_categ_1=1 and categ_2='jkl' then new_var='First categ 1=JKL'; if first_categ_1=1 and categ_2='mno' then new_var='First categ 1=MNO'; if second_categ_1=1 and categ_2='mno' then new_var='Second categ 1=MNO'; if second_categ_1=1 and categ_2='mno' then new_var='Second categ 1=MNO'; run;   This works fine and gives me the information I want; the issue is that I want to exclude anyone who only has one value for categ_1 from my frequencies. I'm not sure how to keep both rows for people with two rows with different values of categ_1 and get rid of the rows for those who only have one row with one value of categ_1. I don't want to collapse because of new_var which tells me what happened to each person's first categ_1 and second categ_1.    Any help is much appreciated. ... View more

Hi all,   I have a dataset at the person/month/categorical group variable level, like this:   ID      First name        Last name       Month       Categorical var 1        abc                    def                  200901              a 1        abc                    def                  200902              a 2        abc                    def                  200903              a 3        abc                    def                  200901              b 4        abc                    def                  200902              b   I'm trying to clean the ID variable by setting all values of ID within levels of the categorical variable, as the value of ID as of the most recent month. So basically, as long as a person has the same name, I assume they're the same person and overwrite older values of ID (within categorical var=a) with the newest value, like this:   ID      First name        Last name       Month       Categorical var 2        abc                    def                  200901              a 2        abc                    def                  200902              a 2        abc                    def                  200903              a 4        abc                    def                  200901              b 4        abc                    def                  200902              b   So basically, I want to retain all the same data, but with ID now consistent within each level of categorical var for each person.    Any help is much appreciated.  ... View more