Hello Everyone, I would like to select the max(letter_date) from each customer and compare it to the due_date.  For some reason the query return every row (not just the row with max letter_date).  I have included sample data and my query.  Any input would be greatly appreciated! Thanks! cust         letter_date      due_date 11111      5-29-2013       1-20-2013 11111      8-10-2011       1-20-2013 22222      4-28-2012       2-20-2013 33333      8-19-2012       1-12-2013 proc sql; create table redefault as select distinct a.cust, b.letter_date, a.due_date from      tab1 a      join tab2 b      on a.cust = b.cust group by b.cust having b.letter_date = max(b.letter_date) and      (max(b.letter_date) < a.due_date) order by a.cust ; quit ... View more

Hello everyone, I am trying to select letter data where the max letter_date for each customer (each customer has multiple letter_dates) is less than the resp_date.  In other words, I need the correspoinding data for the row that has the max letter_date.  Is my having clause correct? proc sql; create table tab1 as select a.cust_id, b.letter_date, a.resp_date, a.letter_type from cust_table  a left join letter_table  b on a.cust_id = b.cust_id group by b.cust_id having (b.letter_date = max(b.letter_date)) and (b.letter_date < a.resp_date) order by a.cust_id ; quit; Thank you in advance for any input! ... View more

Hello Everyone, I am merging two datasets: old and new.   Dataset old has historical data from prior dates and contains 900 columns.  Dataset new has 800 columns and contains new data for today, and hese 800 columns also exist in OLD.  I would like to update the common 800 columns in OLD with value from NEW and keep the other 100 columns in OLD as is where cust_id is in both OLD and NEW.    The code below attemps to do that.  But I couldn't possibly list all 800 columns in the data step.  See the sample data below.  Dataset OUT1 has the 1st 100 columns from OLD and columns 101 to column 900 are populated with value from NEW.  I hope this makes sense.  Does anyone have any suggestions? data out1; merge old (in=a) new (in=b); by cust_id; if a or b; if a = 0 and b = 1 then col1 = new_col1; if a = 0 and b = 1 then col2 = new_col2; .. run; Dataset OLD: cust_id col1 col2 col3 ...col100, col101, col102,..  col898, col899, col 900 111111   aa   bb   cc ....   dd       gg         kk          ff         ll          kk 222222   bb   cc    dd ....   bc        ee         nn          qq        tt         gg Dataset NEW: cust_id col1 col2... col798, col799, col800 111111 uu   ww....   xx       yy       zz        Dataset OUT1: cust_id col1 col2 col3 ... col100, col101, col102,..  col898, col899, col900 111111  aa    bb   cc....    dd        uu      ww....     xx       yy       zz ... View more

Hello Everyone, I have a table that contains hundreds of columns with duplicate unique keys (customer ID). I would like to get rid of the duplicates by selecting the row that contains the max values in a number of columns. For each customer, I would like to keep the row that has the max value in date1, date2, date3, date4, date5,.... etc. The tricky part is there are hundreds of dates I need to check. Is there an efficient way to do this? Here is a sample record: cust_id date1 date2 date3 date4 date5 date6 date7 date8 ... date300 11111 2/2 3/3 4/4 5/5 6/6 7/7 8/8 9/9 .... 12/12 11111 2/1 3/1 4/1 5/1 6/1 7/1 8/1 9/1 .... 12/01 I would like to keep the first row since it has the max value in every column. Any input will be greatly appreciated ! Thank you  ... View more