Hi all-- I am working with a very large data set and I need to identify some problem cases. I have to find the cases which are being dropped where the MAX of CLOSINGS_ID for the CASE_ID group is BLANK/NULL and an earlier CLOSE date (a calculated variable) has been picked up. The cases I’m trying to hunt down look like this: CASE_ID CLOSING_DT_1 CLOSING_DT_2 CLOSINGS_ID CLOSE 111 . 7-Dec-11 1 7-Dec-11 111 7-Oct-11 . 2 7-Oct-11 111 . 3 . 111 . . 4 . I am using these two programs below. One with the HAVING clause and then one without the HAVING clause and then trying all sorts of different ways to combine them together to find with cases which are dropped when the have HAVING clause is used. I can’t figure it out. I just want a list of all these problem cases. Is there a way to do this is Base SAS as well as using Proc SQL as well? Any help is greatly appreciated!! Proc sql; create table With_HAVING as select distinct CASE_ ID, CLOSING_DT_1, CLOSING_DT_2 CLOSINGS_ID, case when CLOSING_DT_2 gt .then CLOSING_DT_2 when CLOSING_DT_1 gt . then CLOSING_DT_1 end as CLOSE from DATA_HAVE group by CASE_ ID, CLOSINGS_ID having CLOSINGS_ID =max(CLOSINGS_ID) ;quit; Proc sql; create table With_NO_HAVING as select distinct CASE_ ID, CLOSING_DT_1, CLOSING_DT_2 CLOSINGS_ID, case when CLOSING_DT_2 gt .then CLOSING_DT_2 when CLOSING_DT_1 gt . then CLOSING_DT_1 end as CLOSE from DATA_HAVE ;quit;
... View more
