Just to clarify further since you are new to SAS and I think effective_date may be what you want in the first sort: /*Sort the data in the right order */ Proc sort data=have ; by userid descending effective_date; run; /*Allows proc sort to eliminate the duplicates and keeps the first row it finds (which is why you needed to sort it to begin with)*/ /** this sorts the above data and creates two new datasets: one with the deduped results (have2) and one with the rows that were removed (dups)**/ proc sort data=have out=have2 dup=dups nodupkey; by userid; run; Hope this helps! EJ ... View more

Thanks everyone ... I thought it was something like that but I couldnt explain it very well but the technote was perfect EJ ... View more

Thanks ! Something like this is where we will end up Im sure. We are actually trying to validate a stored procedure in SQL using SAS so we were trying to reduce the rounding as much as possible to eliminate steps were mismatching can occur. EJ ... View more

Thanks for the response but our values are all positive or zero. EJ ... View more

Im not sure how to respond ... the posted code when used with the sample provides exactly what you show your desired output to be: So Im not sure how to help you. EJ ... View more

So here so further explanation of what I did: the comma in the from statement of sql means an INNER JOIN of the two datasets listed. Inner join return the variables youve asked for in the select statement (a. and b. indicate which dataset that piece comes from), where the two datasets match on the where criteria specified (in this case they have to match on industry year and within the roa range specified). If there are no matches then there will not be a row returned from the inner join (you could change to a LEFT JOIN (switch the "where" to an "on" and the comma to "left join") which would return at least one row for every observation in sample and multiple rows if there are multiple matches from totalcontrol (the single no match row would have the information from sample but empty fields for the totalcontrol variables). To answer the question about deleting observations is harder not knowing your data well. If they are exact matches then you might have duplicates in one of the source files based on the three criteria given. You can add the "distinct" keyword right after the select which should get rid of exact duplicates. I imagine most are not exact duplicates because I am assuming the range on the roa is allowing multiple matches. Hope that helps! EJ ... View more

I dont have a easy way to test this but something like the following should work (may have to play with the variable name in the where statements and you might have to break the where into two separate statements ... ie .... and placement <= "&end"D 😞 data want (keep=flag id onevar);      length flag $3;      set have (in=a rename=(placement=onevar) where=("&start"D <=PLACEMENT <= "&end"D))           have (in=b rename=(removal=onevar) where=("&start"D <=removal <= "&end"D));      if a then flag = "IN";      if b then flag = "OUT"; run;      Hopefully this at least puts you on the right track. EJ ... View more

I guess I should have also asked whether you mean you want the data appended or merged ... I wasnt clear from your description. EJ ... View more

Does the method matter? Data step vs sql? EJ ... View more

It looks like to me you are merging on just industry so I guessing you are getting a cartesian product for like industries between the two files. So here are the assumptions I am using to figure out what you want: 1) Both files were created from the same source file but the 5000 are the suspect = 1 (or what ever) and the totalcontrol is all the rest?? 2) you dont want to match company to company but you want to find a like company from the non suspect pool that is like the companies in the suspect pool??? If so  you will probably get multiples and will have to have another step to pick the ones for each suspect that you want based on some criteria. I would try something like the following (untested): proc sql;      create table potentialmatch1 as      select a.year, a.sic, a.id, a.roa, b.id, b.roa      from sample as a, totalcontrol as b      where a. sic = b.sic and a.year = b.year and a.roa between (.7*b.roa) and (1.3*b.roa) ; quit; See if that helps EJ ... View more

Can you provide the code you are using? Perhaps a little more details about your issue and data would help as well. EJ ... View more