Merging SAS rows with COALESCE function
I am trying to combine the following rows in SAS. Here is the data:
StudentNumber Test1 Test2 Test3
001 . 86 .
001 94 . .
001 . . 75
002 68 . .
002 . 82 .
002 . . 97
I'd like the rows to look like the following:
StudentNumber Test1 Test2 Test3
001 94 86 75
002 68 82 97
I'm used to merging columns with the COALESCE function, but I'm not sure how to do this with rows.
sas coalesce
asked Nov 26 '18 at 14:34
statsguyzstatsguyz
1591316
add a comment |
I am trying to combine the following rows in SAS. Here is the data:
StudentNumber Test1 Test2 Test3
001 . 86 .
001 94 . .
001 . . 75
002 68 . .
002 . 82 .
002 . . 97
I'd like the rows to look like the following:
StudentNumber Test1 Test2 Test3
001 94 86 75
002 68 82 97
I'm used to merging columns with the COALESCE function, but I'm not sure how to do this with rows.
sas coalesce
asked Nov 26 '18 at 14:34
statsguyzstatsguyz
1591316
add a comment |
I am trying to combine the following rows in SAS. Here is the data:
StudentNumber Test1 Test2 Test3
001 . 86 .
001 94 . .
001 . . 75
002 68 . .
002 . 82 .
002 . . 97
I'd like the rows to look like the following:
StudentNumber Test1 Test2 Test3
001 94 86 75
002 68 82 97
I'm used to merging columns with the COALESCE function, but I'm not sure how to do this with rows.
sas coalesce
asked Nov 26 '18 at 14:34
statsguyzstatsguyz
1591316
I am trying to combine the following rows in SAS. Here is the data:
StudentNumber Test1 Test2 Test3
001 . 86 .
001 94 . .
001 . . 75
002 68 . .
002 . 82 .
002 . . 97
I'd like the rows to look like the following:
StudentNumber Test1 Test2 Test3
001 94 86 75
002 68 82 97
I'm used to merging columns with the COALESCE function, but I'm not sure how to do this with rows.
sas coalesce
sas coalesce
asked Nov 26 '18 at 14:34
statsguyzstatsguyz
1591316
asked Nov 26 '18 at 14:34
statsguyzstatsguyz
1591316
asked Nov 26 '18 at 14:34
statsguyzstatsguyz
1591316
asked Nov 26 '18 at 14:34
statsguyzstatsguyz
1591316
asked Nov 26 '18 at 14:34
statsguyzstatsguyz
1591316
1591316
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can use the UPDATE statement to do that. The update statement expects to have a master dataset with unique observations per BY group and a transaction dataset that could have multiple observations per BY group. Only the non-missing values of the transactions will change the values. The output will have one observation per BY group with all transactions applied.
You can use your existing data as both the master and the transaction datasets by adding the dataset option obs=0 to the first reference.
data want;
update have(obs=0) have;
by studentnumber;
run;
answered Nov 26 '18 at 14:50
TomTom
23.5k2718
Yes, this worked! Initially there was a bug in my code, but this is perfect!
– statsguyz
Nov 26 '18 at 15:40
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%2f53483373%2fmerging-sas-rows-with-coalesce-function%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
You can use the UPDATE statement to do that. The update statement expects to have a master dataset with unique observations per BY group and a transaction dataset that could have multiple observations per BY group. Only the non-missing values of the transactions will change the values. The output will have one observation per BY group with all transactions applied.
You can use your existing data as both the master and the transaction datasets by adding the dataset option obs=0 to the first reference.
data want;
update have(obs=0) have;
by studentnumber;
run;
answered Nov 26 '18 at 14:50
TomTom
23.5k2718
Yes, this worked! Initially there was a bug in my code, but this is perfect!
– statsguyz
Nov 26 '18 at 15:40
add a comment |
You can use the UPDATE statement to do that. The update statement expects to have a master dataset with unique observations per BY group and a transaction dataset that could have multiple observations per BY group. Only the non-missing values of the transactions will change the values. The output will have one observation per BY group with all transactions applied.
You can use your existing data as both the master and the transaction datasets by adding the dataset option obs=0 to the first reference.
data want;
update have(obs=0) have;
by studentnumber;
run;
answered Nov 26 '18 at 14:50
TomTom
23.5k2718
Yes, this worked! Initially there was a bug in my code, but this is perfect!
– statsguyz
Nov 26 '18 at 15:40
add a comment |
You can use the UPDATE statement to do that. The update statement expects to have a master dataset with unique observations per BY group and a transaction dataset that could have multiple observations per BY group. Only the non-missing values of the transactions will change the values. The output will have one observation per BY group with all transactions applied.
You can use your existing data as both the master and the transaction datasets by adding the dataset option obs=0 to the first reference.
data want;
update have(obs=0) have;
by studentnumber;
run;
answered Nov 26 '18 at 14:50
TomTom
23.5k2718
You can use the UPDATE statement to do that. The update statement expects to have a master dataset with unique observations per BY group and a transaction dataset that could have multiple observations per BY group. Only the non-missing values of the transactions will change the values. The output will have one observation per BY group with all transactions applied.
You can use your existing data as both the master and the transaction datasets by adding the dataset option obs=0 to the first reference.
data want;
update have(obs=0) have;
by studentnumber;
run;
answered Nov 26 '18 at 14:50
TomTom
23.5k2718
edited Nov 27 '18 at 17:31
answered Nov 26 '18 at 14:50
TomTom
23.5k2718
answered Nov 26 '18 at 14:50
TomTom
23.5k2718
answered Nov 26 '18 at 14:50
TomTom
23.5k2718
23.5k2718
Yes, this worked! Initially there was a bug in my code, but this is perfect!
– statsguyz
Nov 26 '18 at 15:40
add a comment |
Yes, this worked! Initially there was a bug in my code, but this is perfect!
– statsguyz
Nov 26 '18 at 15:40
Yes, this worked! Initially there was a bug in my code, but this is perfect!
– statsguyz
Nov 26 '18 at 15:40
Yes, this worked! Initially there was a bug in my code, but this is perfect!
– statsguyz
Nov 26 '18 at 15:40
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%2f53483373%2fmerging-sas-rows-with-coalesce-function%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
