SAS: Concat columns from different tables (same number of rows)
I have two tables with the same number of rows but not one column I could join them toghether. Like:
data table1(keep=Model) table2(keep=MSRP);
set sashelp.cars;
run;
How cound I concat table1 and table2 in order to achieve:
In python I would do it as pandas.concat([table1, table2], axis=1) but here anything I try like:
data cancated;
set table1 table2;
run;
or
proc sql;
create table joined as
select * from table1
union
select * from table2;
delete from joined where Model is missing or MSRP is missing;
run;
but especially the second one gave me error:
ERROR: Column 1 from the first contributor of UNION is not the same
type as its counterpart from the second.
So if I udnerstand corectly I cannot have this kind of join with different types of variables.
Thanks!
merge sas
asked Nov 25 '18 at 15:01
Mateusz KonopelskiMateusz Konopelski
3141415
add a comment |
I have two tables with the same number of rows but not one column I could join them toghether. Like:
data table1(keep=Model) table2(keep=MSRP);
set sashelp.cars;
run;
How cound I concat table1 and table2 in order to achieve:
In python I would do it as pandas.concat([table1, table2], axis=1) but here anything I try like:
data cancated;
set table1 table2;
run;
or
proc sql;
create table joined as
select * from table1
union
select * from table2;
delete from joined where Model is missing or MSRP is missing;
run;
but especially the second one gave me error:
ERROR: Column 1 from the first contributor of UNION is not the same
type as its counterpart from the second.
So if I udnerstand corectly I cannot have this kind of join with different types of variables.
Thanks!
merge sas
asked Nov 25 '18 at 15:01
Mateusz KonopelskiMateusz Konopelski
3141415
Note that this is not good data management practice to assume that the observations are actually in matching order without any identifier variables that can be used to verify that you are combining the right observations. Just because you can do it doesn't mean you should.
– Tom
Nov 25 '18 at 15:34
@Tom Thank you for your note. In my specific case I will have always the same number of rows and the same order - therefore your solution is perfect.
– Mateusz Konopelski
Nov 27 '18 at 7:42
add a comment |
I have two tables with the same number of rows but not one column I could join them toghether. Like:
data table1(keep=Model) table2(keep=MSRP);
set sashelp.cars;
run;
How cound I concat table1 and table2 in order to achieve:
In python I would do it as pandas.concat([table1, table2], axis=1) but here anything I try like:
data cancated;
set table1 table2;
run;
or
proc sql;
create table joined as
select * from table1
union
select * from table2;
delete from joined where Model is missing or MSRP is missing;
run;
but especially the second one gave me error:
ERROR: Column 1 from the first contributor of UNION is not the same
type as its counterpart from the second.
So if I udnerstand corectly I cannot have this kind of join with different types of variables.
Thanks!
merge sas
asked Nov 25 '18 at 15:01
Mateusz KonopelskiMateusz Konopelski
3141415
I have two tables with the same number of rows but not one column I could join them toghether. Like:
data table1(keep=Model) table2(keep=MSRP);
set sashelp.cars;
run;
How cound I concat table1 and table2 in order to achieve:
In python I would do it as pandas.concat([table1, table2], axis=1) but here anything I try like:
data cancated;
set table1 table2;
run;
or
proc sql;
create table joined as
select * from table1
union
select * from table2;
delete from joined where Model is missing or MSRP is missing;
run;
but especially the second one gave me error:
ERROR: Column 1 from the first contributor of UNION is not the same
type as its counterpart from the second.
So if I udnerstand corectly I cannot have this kind of join with different types of variables.
Thanks!
merge sas
merge sas
asked Nov 25 '18 at 15:01
Mateusz KonopelskiMateusz Konopelski
3141415
asked Nov 25 '18 at 15:01
Mateusz KonopelskiMateusz Konopelski
3141415
edited Nov 25 '18 at 15:21
Mateusz Konopelski
asked Nov 25 '18 at 15:01
Mateusz KonopelskiMateusz Konopelski
3141415
asked Nov 25 '18 at 15:01
Mateusz KonopelskiMateusz Konopelski
3141415
asked Nov 25 '18 at 15:01
Mateusz KonopelskiMateusz Konopelski
3141415
3141415
Note that this is not good data management practice to assume that the observations are actually in matching order without any identifier variables that can be used to verify that you are combining the right observations. Just because you can do it doesn't mean you should.
– Tom
Nov 25 '18 at 15:34
@Tom Thank you for your note. In my specific case I will have always the same number of rows and the same order - therefore your solution is perfect.
– Mateusz Konopelski
Nov 27 '18 at 7:42
add a comment |
Note that this is not good data management practice to assume that the observations are actually in matching order without any identifier variables that can be used to verify that you are combining the right observations. Just because you can do it doesn't mean you should.
– Tom
Nov 25 '18 at 15:34
@Tom Thank you for your note. In my specific case I will have always the same number of rows and the same order - therefore your solution is perfect.
– Mateusz Konopelski
Nov 27 '18 at 7:42
Note that this is not good data management practice to assume that the observations are actually in matching order without any identifier variables that can be used to verify that you are combining the right observations. Just because you can do it doesn't mean you should.
– Tom
Nov 25 '18 at 15:34
Note that this is not good data management practice to assume that the observations are actually in matching order without any identifier variables that can be used to verify that you are combining the right observations. Just because you can do it doesn't mean you should.
– Tom
Nov 25 '18 at 15:34
@Tom Thank you for your note. In my specific case I will have always the same number of rows and the same order - therefore your solution is perfect.
– Mateusz Konopelski
Nov 27 '18 at 7:42
@Tom Thank you for your note. In my specific case I will have always the same number of rows and the same order - therefore your solution is perfect.
– Mateusz Konopelski
Nov 27 '18 at 7:42
add a comment |
2 Answers
2
active
oldest
votes
You can use a merge statement without any by statement to get a row-by-row matching of the observations from two or more datasets.
data concated;
merge table1 table2;
run;
You could also just use separate set statements for each dataset.
data concated;
set table1;
set table2;
run;
The difference will be when the two datasets have different number of observations. With merge the number of observations will match that of the larger dataset. (The variables contributed only from the smaller dataset will have their values retained.) With the set statements the result will only have the number of observations in the smaller dataset. The step will end when either of the set statements reads past the end of the input dataset.
answered Nov 25 '18 at 15:30
TomTom
24.3k2720
add a comment |
to do something like in your query. you need to use row number as shown below and then join and delete.
data table1(keep=Model var) table2(keep=MSRP var);
set sashelp.cars;
var = _n_;
run;
proc sql;
create table joined(drop=var) as
select a.*, b.* from table1 a
full join table2 b
on a.var = b.var;
delete from joined where Model is missing or MSRP is missing
;
answered Nov 25 '18 at 16:06
Kiran Kiran
2,96531020
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%2f53468776%2fsas-concat-columns-from-different-tables-same-number-of-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use a merge statement without any by statement to get a row-by-row matching of the observations from two or more datasets.
data concated;
merge table1 table2;
run;
You could also just use separate set statements for each dataset.
data concated;
set table1;
set table2;
run;
The difference will be when the two datasets have different number of observations. With merge the number of observations will match that of the larger dataset. (The variables contributed only from the smaller dataset will have their values retained.) With the set statements the result will only have the number of observations in the smaller dataset. The step will end when either of the set statements reads past the end of the input dataset.
answered Nov 25 '18 at 15:30
TomTom
24.3k2720
add a comment |
You can use a merge statement without any by statement to get a row-by-row matching of the observations from two or more datasets.
data concated;
merge table1 table2;
run;
You could also just use separate set statements for each dataset.
data concated;
set table1;
set table2;
run;
The difference will be when the two datasets have different number of observations. With merge the number of observations will match that of the larger dataset. (The variables contributed only from the smaller dataset will have their values retained.) With the set statements the result will only have the number of observations in the smaller dataset. The step will end when either of the set statements reads past the end of the input dataset.
answered Nov 25 '18 at 15:30
TomTom
24.3k2720
add a comment |
You can use a merge statement without any by statement to get a row-by-row matching of the observations from two or more datasets.
data concated;
merge table1 table2;
run;
You could also just use separate set statements for each dataset.
data concated;
set table1;
set table2;
run;
The difference will be when the two datasets have different number of observations. With merge the number of observations will match that of the larger dataset. (The variables contributed only from the smaller dataset will have their values retained.) With the set statements the result will only have the number of observations in the smaller dataset. The step will end when either of the set statements reads past the end of the input dataset.
answered Nov 25 '18 at 15:30
TomTom
24.3k2720
You can use a merge statement without any by statement to get a row-by-row matching of the observations from two or more datasets.
data concated;
merge table1 table2;
run;
You could also just use separate set statements for each dataset.
data concated;
set table1;
set table2;
run;
The difference will be when the two datasets have different number of observations. With merge the number of observations will match that of the larger dataset. (The variables contributed only from the smaller dataset will have their values retained.) With the set statements the result will only have the number of observations in the smaller dataset. The step will end when either of the set statements reads past the end of the input dataset.
answered Nov 25 '18 at 15:30
TomTom
24.3k2720
answered Nov 25 '18 at 15:30
TomTom
24.3k2720
answered Nov 25 '18 at 15:30
TomTom
24.3k2720
answered Nov 25 '18 at 15:30
TomTom
24.3k2720
24.3k2720
add a comment |
add a comment |
to do something like in your query. you need to use row number as shown below and then join and delete.
data table1(keep=Model var) table2(keep=MSRP var);
set sashelp.cars;
var = _n_;
run;
proc sql;
create table joined(drop=var) as
select a.*, b.* from table1 a
full join table2 b
on a.var = b.var;
delete from joined where Model is missing or MSRP is missing
;
answered Nov 25 '18 at 16:06
Kiran Kiran
2,96531020
add a comment |
to do something like in your query. you need to use row number as shown below and then join and delete.
data table1(keep=Model var) table2(keep=MSRP var);
set sashelp.cars;
var = _n_;
run;
proc sql;
create table joined(drop=var) as
select a.*, b.* from table1 a
full join table2 b
on a.var = b.var;
delete from joined where Model is missing or MSRP is missing
;
answered Nov 25 '18 at 16:06
Kiran Kiran
2,96531020
add a comment |
to do something like in your query. you need to use row number as shown below and then join and delete.
data table1(keep=Model var) table2(keep=MSRP var);
set sashelp.cars;
var = _n_;
run;
proc sql;
create table joined(drop=var) as
select a.*, b.* from table1 a
full join table2 b
on a.var = b.var;
delete from joined where Model is missing or MSRP is missing
;
answered Nov 25 '18 at 16:06
Kiran Kiran
2,96531020
to do something like in your query. you need to use row number as shown below and then join and delete.
data table1(keep=Model var) table2(keep=MSRP var);
set sashelp.cars;
var = _n_;
run;
proc sql;
create table joined(drop=var) as
select a.*, b.* from table1 a
full join table2 b
on a.var = b.var;
delete from joined where Model is missing or MSRP is missing
;
answered Nov 25 '18 at 16:06
Kiran Kiran
2,96531020
answered Nov 25 '18 at 16:06
Kiran Kiran
2,96531020
answered Nov 25 '18 at 16:06
Kiran Kiran
2,96531020
answered Nov 25 '18 at 16:06
Kiran Kiran
2,96531020
2,96531020
add a comment |
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%2f53468776%2fsas-concat-columns-from-different-tables-same-number-of-rows%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
Note that this is not good data management practice to assume that the observations are actually in matching order without any identifier variables that can be used to verify that you are combining the right observations. Just because you can do it doesn't mean you should.
– Tom
Nov 25 '18 at 15:34
@Tom Thank you for your note. In my specific case I will have always the same number of rows and the same order - therefore your solution is perfect.
– Mateusz Konopelski
Nov 27 '18 at 7:42