Hi, I have two datasets. One has ID number and several categorical variables, like this: ID     cat_var1    cat_var2     cat_var3 1            a                d                 i 2            b                f                 h 3            c                e                 j 4            a                e                h 5            c                d                h Dataset 1 is unique at the ID level. Dataset 2 has an ID variable; a numeric variable, which always equals 1; and a group variable. Each ID recurs several times in Dataset 2, like this: ID     count_var     group_var 1          1                        1 1          1                        1 1          1                        2     1          1                        2 1          1                        2 2          1                        1 2          1                        1 2          1                        1 What I want is to sum the count_var by ID and group (that is, ID=1 would have sum=2 for group 1 and sum=3 for group 2) and add the sum variables to Dataset 1. I know the inefficient way to do this--do a proc sql sum of count_var grouping by ID and group_var, then transpose, then join to Dataset 1 on ID--but I'm hoping there's a more efficient way to get what I want. Any help is much appreciated. ... View more

Hi, I have a dataset of duplicates--each ID occurs twice (but the values of other variables can differ within the same ID), like this: ID     Var1     Var2 1        1            0 1        0            1 2        0            0 2        0            1 3        0            0 3        1            1 I want to keep one row for each ID, but I need to do it conditionally. First, if they have a value of 1 for Var1, that is the row that should be kept (each pair of rows will either have one 0 and one 1 for Var1 or two 0s). If a pair of rows has two 0s for Var1, the one with a value of 1 for Var2 should be kept. Any help is much appreciated! ... View more

Hi, I am trying to join two datasets on an ID variable present in both datasets. However, in one dataset, the ID is formatted to always be at least 3 digits by adding leading 0s if the ID is only a 1- or 2-digit number (so like 001, 050, 067, etc). In the other dataset, the ID variable does not have the leading 0s, and the same values that I used as examples before would be 1, 50, 67, etc. I'm not sure how to match on these values considering the mismatch. Trimming the 0s would be difficult because some IDs have two leading 0s, some one leading 0, and others zero leading 0s. Within each dataset, the IDs have different lengths (either 3 or 4). Any help is much appreciated. Thanks! ... View more

Hi, I have a dataset with about 100 variables all starting with the same prefix (and ending in different numbers, like prefix1, prefix3, prefix7...prefix170, etc.). The numbers are not consecutive. All I want to do is rename the variables prefix1_2008, prefix3_2008, etc. There are too many variables to type them all by hand, so I'm hoping there's some kind of shortcut. Any help is much appreciated. Thanks! ... View more

Hi all, I have data at the person-event level--that is, each row represents one event for one person. The data include ID number, a fixed date at the person level (ie, each time an ID recurs, this date will be the same), an event type variable, and an event date variable. IDs can recur. ID          Fixed date          Event type          Event date 1             5/4/2013               A                    10/12/2012 1             5/4/2013               B                     6/7/2013 1             5/4/2013               A                     1/1/2013 2             9/2/2012               B                     10/4/2012 2             9/2/2012               B                     11/1/2012 3            10/4/2014              A                      3/4/2013 What I want to be able to evaluate is whether a specific type of event occurred within 6 months of the fixed date (that is, whether the event date is within 6 months of the fixed date). Any help is much appreciated. ... View more

Hi, I have two datasets. One has many variables, one of which is a unique ID variable (1 row=1 unique person, no dupes). The second dataset is just a list of IDs. What I want to do is create a variable in Dataset 1 for whether a person's ID also appears in Dataset 2. I know how to do this using joins, but I'm hoping there's a simpler way that I don't know about or haven't thought of. Any help is much appreciated. Thanks! ... View more

Hi, I am trying to import an Excel spreadsheet into SAS Enterprise Guide 4.3 using the following code: proc import out=cohort datafile="\\C:\Datafile.xlsx" dbms=excel replace; run; When I run this, I get an error message saying that dbms type Excel is not valid for import. If I replace excel with xlsx in the code, I get the same error message. Any help is much appreciated. ... View more

Hi all, I am trying to get the sum of a column in a dataset. I tried using both proc means and proc sql, but in both cases, the number of digits in the sum exceeded the length of the variable. I'm not sure how to program a longer length into either of those functions. I tried to do it in proc sql like this: proc sql; create table new as select sum(var) as sum_var length=50 from have; quit; The code worked, but ignored my length statement. Any help is much appreciated. ... View more