Pfthb

I currently run SQL to give me a daily position of circa 650k accounts. I have 2 pieces of code which will show me any accounts which have dropped off the list compared to the previous day or any which have been added onto the list from the previous day.

I am doing this as follows;

data MOVECHECK121118Drop;
merge MOVECHECK091118(in=a)
MOVECheck121118(in=b);
If A and not B;
Run;

data MOVECHECK121118Gain;
merge MOVECHECK091118(in=a)
MOVECheck121118(in=b);
If B and not A;
Run;

The Gain code works correctly and gives me no results, the drop code should be giving me one result as i know one movement has happened.
Although the drop code does give me one result, it does not give me the correct result, the account it identifies is present in both datasets within the merge and happens to be the last entry in each list, i am therefore unsure why it is pulling back this result,

I am assuming i am doing something wrong within the code??

Any help is greatly appreciated,

In a SAS merge step you almost always include a BY statement which lists the variables to merge by (like SQL join on). Your code would look like:

data MOVECHECK121118Drop ;
 merge MOVECHECK091118(in=a)
 MOVECheck121118(in=b) 
 ;
 by SomeIDvariable ;
 if A and not B;
run;

The subsetting IF statement would select records that are in MOVECHECK091118 but there is no matching value of SomeIDvariable in MOVECheck121118.

Without a BY statement, SAS does a sequential merge (a 'smush'), merging the first record of each dataset, the second record of each dataset, etc. There is no ID variable to match by.

Many folks believe a merge with no BY statement should produce an error. If you agree, you can set options mergenoby=error; . That will throw an error if you accidentally forgetting the BY statement.