Data Merge If A Not B
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,
sql sas teradata
|
show 4 more comments
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,
sql sas teradata
1
Please tag the DBMS.
– Mohammad Mohabbati
Nov 13 '18 at 8:56
1
Is that code supposed to be SQL? Must be some product extension...
– jarlh
Nov 13 '18 at 8:58
1
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your code uses non-standard syntax). Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ...
– WhatsThePoint
Nov 13 '18 at 9:08
I believe it is Teradata
– Kelvin Withington
Nov 13 '18 at 10:02
This is not SQL, what's the client your're using?
– dnoeth
Nov 13 '18 at 12:31
|
show 4 more comments
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,
sql sas teradata
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,
sql sas teradata
sql sas teradata
edited Nov 13 '18 at 17:15
JNevill
31.8k31544
31.8k31544
asked Nov 13 '18 at 8:55


Kelvin WithingtonKelvin Withington
1
1
1
Please tag the DBMS.
– Mohammad Mohabbati
Nov 13 '18 at 8:56
1
Is that code supposed to be SQL? Must be some product extension...
– jarlh
Nov 13 '18 at 8:58
1
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your code uses non-standard syntax). Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ...
– WhatsThePoint
Nov 13 '18 at 9:08
I believe it is Teradata
– Kelvin Withington
Nov 13 '18 at 10:02
This is not SQL, what's the client your're using?
– dnoeth
Nov 13 '18 at 12:31
|
show 4 more comments
1
Please tag the DBMS.
– Mohammad Mohabbati
Nov 13 '18 at 8:56
1
Is that code supposed to be SQL? Must be some product extension...
– jarlh
Nov 13 '18 at 8:58
1
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your code uses non-standard syntax). Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ...
– WhatsThePoint
Nov 13 '18 at 9:08
I believe it is Teradata
– Kelvin Withington
Nov 13 '18 at 10:02
This is not SQL, what's the client your're using?
– dnoeth
Nov 13 '18 at 12:31
1
1
Please tag the DBMS.
– Mohammad Mohabbati
Nov 13 '18 at 8:56
Please tag the DBMS.
– Mohammad Mohabbati
Nov 13 '18 at 8:56
1
1
Is that code supposed to be SQL? Must be some product extension...
– jarlh
Nov 13 '18 at 8:58
Is that code supposed to be SQL? Must be some product extension...
– jarlh
Nov 13 '18 at 8:58
1
1
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your code uses non-standard syntax). Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ...
– WhatsThePoint
Nov 13 '18 at 9:08
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your code uses non-standard syntax). Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ...
– WhatsThePoint
Nov 13 '18 at 9:08
I believe it is Teradata
– Kelvin Withington
Nov 13 '18 at 10:02
I believe it is Teradata
– Kelvin Withington
Nov 13 '18 at 10:02
This is not SQL, what's the client your're using?
– dnoeth
Nov 13 '18 at 12:31
This is not SQL, what's the client your're using?
– dnoeth
Nov 13 '18 at 12:31
|
show 4 more comments
1 Answer
1
active
oldest
votes
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.
Thank you, i cannot believe i have missed the BY statement, this is something which is included in ALL other code which contains a merge, thanks for your help,
– Kelvin Withington
Nov 14 '18 at 8:30
Be sure to mark Quentin’s answer as correct if it solved the problem in your question.
– Rob Paller
Nov 16 '18 at 23:59
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53277160%2fdata-merge-if-a-not-b%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Thank you, i cannot believe i have missed the BY statement, this is something which is included in ALL other code which contains a merge, thanks for your help,
– Kelvin Withington
Nov 14 '18 at 8:30
Be sure to mark Quentin’s answer as correct if it solved the problem in your question.
– Rob Paller
Nov 16 '18 at 23:59
add a comment |
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.
Thank you, i cannot believe i have missed the BY statement, this is something which is included in ALL other code which contains a merge, thanks for your help,
– Kelvin Withington
Nov 14 '18 at 8:30
Be sure to mark Quentin’s answer as correct if it solved the problem in your question.
– Rob Paller
Nov 16 '18 at 23:59
add a comment |
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.
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.
answered Nov 13 '18 at 18:38
QuentinQuentin
4,8321819
4,8321819
Thank you, i cannot believe i have missed the BY statement, this is something which is included in ALL other code which contains a merge, thanks for your help,
– Kelvin Withington
Nov 14 '18 at 8:30
Be sure to mark Quentin’s answer as correct if it solved the problem in your question.
– Rob Paller
Nov 16 '18 at 23:59
add a comment |
Thank you, i cannot believe i have missed the BY statement, this is something which is included in ALL other code which contains a merge, thanks for your help,
– Kelvin Withington
Nov 14 '18 at 8:30
Be sure to mark Quentin’s answer as correct if it solved the problem in your question.
– Rob Paller
Nov 16 '18 at 23:59
Thank you, i cannot believe i have missed the BY statement, this is something which is included in ALL other code which contains a merge, thanks for your help,
– Kelvin Withington
Nov 14 '18 at 8:30
Thank you, i cannot believe i have missed the BY statement, this is something which is included in ALL other code which contains a merge, thanks for your help,
– Kelvin Withington
Nov 14 '18 at 8:30
Be sure to mark Quentin’s answer as correct if it solved the problem in your question.
– Rob Paller
Nov 16 '18 at 23:59
Be sure to mark Quentin’s answer as correct if it solved the problem in your question.
– Rob Paller
Nov 16 '18 at 23:59
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53277160%2fdata-merge-if-a-not-b%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Please tag the DBMS.
– Mohammad Mohabbati
Nov 13 '18 at 8:56
1
Is that code supposed to be SQL? Must be some product extension...
– jarlh
Nov 13 '18 at 8:58
1
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your code uses non-standard syntax). Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ...
– WhatsThePoint
Nov 13 '18 at 9:08
I believe it is Teradata
– Kelvin Withington
Nov 13 '18 at 10:02
This is not SQL, what's the client your're using?
– dnoeth
Nov 13 '18 at 12:31