In a sas compare, output only differences and new records
0
In a compare with id, how can I output only the difference and the new records
but not the old records no more present?
Example, suppose I have two tables:
mybase:
key other
1 Ann
3 Ann
4 Charlie
5 Emily
and mycompare:
key other
2 Bill
3 Charlie
4 Charlie
running:
proc compare data=mybase
compare=mycompare
outnoequal
outdif
out=myoutput
listvar
outcomp
outbase
method = absolute
criterion = 0.0001
;
id key;
run;
I get a table "myoutput" like this:
type obs key other
base 1 1 Ann
compare 1 2 Bill
base 2 3 Ann
compare 2 3 Charlie
dif 2 3 XXXXXXX
base 4 5 Emily
I would like to have this:
type obs key other
compare 1 2 Bill
base 2 3 Ann
compare 2 3 Charlie
dif 2 3 XXXXXXX
sas
asked Nov 20 '18 at 15:59
CarLaTeXCarLaTeX
1349
add a comment |
0
In a compare with id, how can I output only the difference and the new records
but not the old records no more present?
Example, suppose I have two tables:
mybase:
key other
1 Ann
3 Ann
4 Charlie
5 Emily
and mycompare:
key other
2 Bill
3 Charlie
4 Charlie
running:
proc compare data=mybase
compare=mycompare
outnoequal
outdif
out=myoutput
listvar
outcomp
outbase
method = absolute
criterion = 0.0001
;
id key;
run;
I get a table "myoutput" like this:
type obs key other
base 1 1 Ann
compare 1 2 Bill
base 2 3 Ann
compare 2 3 Charlie
dif 2 3 XXXXXXX
base 4 5 Emily
I would like to have this:
type obs key other
compare 1 2 Bill
base 2 3 Ann
compare 2 3 Charlie
dif 2 3 XXXXXXX
sas
asked Nov 20 '18 at 15:59
CarLaTeXCarLaTeX
1349
I don't see an option for that in PROC COMPARE. Might be forced to add a pre-processing step where you merge MyBase and MyCompare by ID, and delete any records from MyBase where the ID is not in MyCompare. Then you can run PROC COMPARE with the updated version of MyBase.
– Quentin
Nov 20 '18 at 16:17
1
@Quentin Thank you, I hoped there was :(
– CarLaTeX
Nov 20 '18 at 16:20
add a comment |
0
0
0
In a compare with id, how can I output only the difference and the new records
but not the old records no more present?
Example, suppose I have two tables:
mybase:
key other
1 Ann
3 Ann
4 Charlie
5 Emily
and mycompare:
key other
2 Bill
3 Charlie
4 Charlie
running:
proc compare data=mybase
compare=mycompare
outnoequal
outdif
out=myoutput
listvar
outcomp
outbase
method = absolute
criterion = 0.0001
;
id key;
run;
I get a table "myoutput" like this:
type obs key other
base 1 1 Ann
compare 1 2 Bill
base 2 3 Ann
compare 2 3 Charlie
dif 2 3 XXXXXXX
base 4 5 Emily
I would like to have this:
type obs key other
compare 1 2 Bill
base 2 3 Ann
compare 2 3 Charlie
dif 2 3 XXXXXXX
sas
asked Nov 20 '18 at 15:59
CarLaTeXCarLaTeX
1349
In a compare with id, how can I output only the difference and the new records
but not the old records no more present?
Example, suppose I have two tables:
mybase:
key other
1 Ann
3 Ann
4 Charlie
5 Emily
and mycompare:
key other
2 Bill
3 Charlie
4 Charlie
running:
proc compare data=mybase
compare=mycompare
outnoequal
outdif
out=myoutput
listvar
outcomp
outbase
method = absolute
criterion = 0.0001
;
id key;
run;
I get a table "myoutput" like this:
type obs key other
base 1 1 Ann
compare 1 2 Bill
base 2 3 Ann
compare 2 3 Charlie
dif 2 3 XXXXXXX
base 4 5 Emily
I would like to have this:
type obs key other
compare 1 2 Bill
base 2 3 Ann
compare 2 3 Charlie
dif 2 3 XXXXXXX
sas
sas
asked Nov 20 '18 at 15:59
CarLaTeXCarLaTeX
1349
asked Nov 20 '18 at 15:59
CarLaTeXCarLaTeX
1349
asked Nov 20 '18 at 15:59
CarLaTeXCarLaTeX
1349
asked Nov 20 '18 at 15:59
CarLaTeXCarLaTeX
1349
asked Nov 20 '18 at 15:59
CarLaTeXCarLaTeX
1349
1349
I don't see an option for that in PROC COMPARE. Might be forced to add a pre-processing step where you merge MyBase and MyCompare by ID, and delete any records from MyBase where the ID is not in MyCompare. Then you can run PROC COMPARE with the updated version of MyBase.
– Quentin
Nov 20 '18 at 16:17
1
@Quentin Thank you, I hoped there was :(
– CarLaTeX
Nov 20 '18 at 16:20
add a comment |
I don't see an option for that in PROC COMPARE. Might be forced to add a pre-processing step where you merge MyBase and MyCompare by ID, and delete any records from MyBase where the ID is not in MyCompare. Then you can run PROC COMPARE with the updated version of MyBase.
– Quentin
Nov 20 '18 at 16:17
1
@Quentin Thank you, I hoped there was :(
– CarLaTeX
Nov 20 '18 at 16:20
I don't see an option for that in PROC COMPARE. Might be forced to add a pre-processing step where you merge MyBase and MyCompare by ID, and delete any records from MyBase where the ID is not in MyCompare. Then you can run PROC COMPARE with the updated version of MyBase.
– Quentin
Nov 20 '18 at 16:17
I don't see an option for that in PROC COMPARE. Might be forced to add a pre-processing step where you merge MyBase and MyCompare by ID, and delete any records from MyBase where the ID is not in MyCompare. Then you can run PROC COMPARE with the updated version of MyBase.
– Quentin
Nov 20 '18 at 16:17
1
1
@Quentin Thank you, I hoped there was :(
– CarLaTeX
Nov 20 '18 at 16:20
@Quentin Thank you, I hoped there was :(
– CarLaTeX
Nov 20 '18 at 16:20
add a comment |
1 Answer
1
active
oldest
votes
1
This works for your example. I think you want to output records that are not matched in base and any records that match and have differences.
data mybase;
input key other $;
cards;
1 Ann
3 Ann
4 Charlie
5 Emily
;;;;
data mycompare;
input key other $;
cards;
2 Bill
3 Charlie
4 Charlie
;;;;
proc compare data=mybase
compare=mycompare
outnoequal
outdif
out=myoutput
listvar
outcomp
outbase
method = absolute
criterion = 0.0001
;
id key;
run;
proc print;
run;
data test;
set myoutput;
by key;
if (first.key and last.key) and _type_ eq 'BASE' then delete;
run;
proc print;
run;
Obs _TYPE_ _OBS_ key other
1 COMPARE 1 2 Bill
2 BASE 2 3 Ann
3 COMPARE 2 3 Charlie
4 DIF 1 3 XXXXXXX.
answered Nov 20 '18 at 18:23
data _null_data _null_
5,472610
Your solution works perfectly and is independent on inputs, as I was looking for, thank you!
– CarLaTeX
Nov 21 '18 at 9:55
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
});
}
});
draft saved
draft discarded
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%2f53396890%2fin-a-sas-compare-output-only-differences-and-new-records%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
1
This works for your example. I think you want to output records that are not matched in base and any records that match and have differences.
data mybase;
input key other $;
cards;
1 Ann
3 Ann
4 Charlie
5 Emily
;;;;
data mycompare;
input key other $;
cards;
2 Bill
3 Charlie
4 Charlie
;;;;
proc compare data=mybase
compare=mycompare
outnoequal
outdif
out=myoutput
listvar
outcomp
outbase
method = absolute
criterion = 0.0001
;
id key;
run;
proc print;
run;
data test;
set myoutput;
by key;
if (first.key and last.key) and _type_ eq 'BASE' then delete;
run;
proc print;
run;
Obs _TYPE_ _OBS_ key other
1 COMPARE 1 2 Bill
2 BASE 2 3 Ann
3 COMPARE 2 3 Charlie
4 DIF 1 3 XXXXXXX.
answered Nov 20 '18 at 18:23
data _null_data _null_
5,472610
Your solution works perfectly and is independent on inputs, as I was looking for, thank you!
– CarLaTeX
Nov 21 '18 at 9:55
add a comment |
1
This works for your example. I think you want to output records that are not matched in base and any records that match and have differences.
data mybase;
input key other $;
cards;
1 Ann
3 Ann
4 Charlie
5 Emily
;;;;
data mycompare;
input key other $;
cards;
2 Bill
3 Charlie
4 Charlie
;;;;
proc compare data=mybase
compare=mycompare
outnoequal
outdif
out=myoutput
listvar
outcomp
outbase
method = absolute
criterion = 0.0001
;
id key;
run;
proc print;
run;
data test;
set myoutput;
by key;
if (first.key and last.key) and _type_ eq 'BASE' then delete;
run;
proc print;
run;
Obs _TYPE_ _OBS_ key other
1 COMPARE 1 2 Bill
2 BASE 2 3 Ann
3 COMPARE 2 3 Charlie
4 DIF 1 3 XXXXXXX.
answered Nov 20 '18 at 18:23
data _null_data _null_
5,472610
Your solution works perfectly and is independent on inputs, as I was looking for, thank you!
– CarLaTeX
Nov 21 '18 at 9:55
add a comment |
1
1
1
This works for your example. I think you want to output records that are not matched in base and any records that match and have differences.
data mybase;
input key other $;
cards;
1 Ann
3 Ann
4 Charlie
5 Emily
;;;;
data mycompare;
input key other $;
cards;
2 Bill
3 Charlie
4 Charlie
;;;;
proc compare data=mybase
compare=mycompare
outnoequal
outdif
out=myoutput
listvar
outcomp
outbase
method = absolute
criterion = 0.0001
;
id key;
run;
proc print;
run;
data test;
set myoutput;
by key;
if (first.key and last.key) and _type_ eq 'BASE' then delete;
run;
proc print;
run;
Obs _TYPE_ _OBS_ key other
1 COMPARE 1 2 Bill
2 BASE 2 3 Ann
3 COMPARE 2 3 Charlie
4 DIF 1 3 XXXXXXX.
answered Nov 20 '18 at 18:23
data _null_data _null_
5,472610
This works for your example. I think you want to output records that are not matched in base and any records that match and have differences.
data mybase;
input key other $;
cards;
1 Ann
3 Ann
4 Charlie
5 Emily
;;;;
data mycompare;
input key other $;
cards;
2 Bill
3 Charlie
4 Charlie
;;;;
proc compare data=mybase
compare=mycompare
outnoequal
outdif
out=myoutput
listvar
outcomp
outbase
method = absolute
criterion = 0.0001
;
id key;
run;
proc print;
run;
data test;
set myoutput;
by key;
if (first.key and last.key) and _type_ eq 'BASE' then delete;
run;
proc print;
run;
Obs _TYPE_ _OBS_ key other
1 COMPARE 1 2 Bill
2 BASE 2 3 Ann
3 COMPARE 2 3 Charlie
4 DIF 1 3 XXXXXXX.
answered Nov 20 '18 at 18:23
data _null_data _null_
5,472610
answered Nov 20 '18 at 18:23
data _null_data _null_
5,472610
answered Nov 20 '18 at 18:23
data _null_data _null_
5,472610
answered Nov 20 '18 at 18:23
data _null_data _null_
5,472610
5,472610
Your solution works perfectly and is independent on inputs, as I was looking for, thank you!
– CarLaTeX
Nov 21 '18 at 9:55
add a comment |
Your solution works perfectly and is independent on inputs, as I was looking for, thank you!
– CarLaTeX
Nov 21 '18 at 9:55
Your solution works perfectly and is independent on inputs, as I was looking for, thank you!
– CarLaTeX
Nov 21 '18 at 9:55
Your solution works perfectly and is independent on inputs, as I was looking for, thank you!
– CarLaTeX
Nov 21 '18 at 9:55
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53396890%2fin-a-sas-compare-output-only-differences-and-new-records%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
I don't see an option for that in PROC COMPARE. Might be forced to add a pre-processing step where you merge MyBase and MyCompare by ID, and delete any records from MyBase where the ID is not in MyCompare. Then you can run PROC COMPARE with the updated version of MyBase.
– Quentin
Nov 20 '18 at 16:17
1
@Quentin Thank you, I hoped there was :(
– CarLaTeX
Nov 20 '18 at 16:20