DATA Step, Macro, Functions and more

Merge / Append two differnt columns into one column

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Merge / Append two differnt columns into one column

I have two columns listed below..

Header 1
2
3
4
5
6

Header 1
1
2
5

And I want to create something like this below..

Header 1
1
2
3
4
5
6

Can I do this in SAS data step?


Accepted Solutions
Solution
‎05-19-2015 01:28 PM
Valued Guide
Posts: 860

Re: Merge / Append two differnt columns into one column

Posted in reply to JUN_Sands

You will have to rename, using either way will look like this:

data want;

update have1 have2(rename=(header_two=header));

by header;

run;

proc sql;

create table want as

select header from have1

union

select header_two as header from have2

order by header;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Merge / Append two differnt columns into one column

Posted in reply to JUN_Sands

If the two sets are sorted by the Header variable:

data want;

     merge dataset1 dataset2;

     by Header1;

run;

If you are combining sets with more variables with the same name then the order the datasets appear on the Merge statement become an issue as will if the BY variable has repeated values in both datasets.

Contributor
Posts: 22

Re: Merge / Append two differnt columns into one column

I should have mentioned that two columns have different labels. Do I need to change them to same column name before merge them?

Solution
‎05-19-2015 01:28 PM
Valued Guide
Posts: 860

Re: Merge / Append two differnt columns into one column

Posted in reply to JUN_Sands

You will have to rename, using either way will look like this:

data want;

update have1 have2(rename=(header_two=header));

by header;

run;

proc sql;

create table want as

select header from have1

union

select header_two as header from have2

order by header;

Super User
Posts: 11,343

Re: Merge / Append two differnt columns into one column

Posted in reply to JUN_Sands

If you have any doubt add a label statement to the code. By default variable characteristics such as Label or Format will be the one associated with the first listed data set for those items common to more than one set.

Super Contributor
Posts: 275

Re: Merge / Append two differnt columns into one column

Posted in reply to JUN_Sands

You could use merge or update statement

Valued Guide
Posts: 860

Re: Merge / Append two differnt columns into one column

Posted in reply to JUN_Sands

Union will eliminate the duplicates and you can sort in the same step:

data have1;

infile cards dsd dlm=',';

input header;

cards;

2

3

4

5

6

;

run;

data have2;

infile cards dsd dlm=',';

input header;

cards;

1

2

5

;

run;

proc sql;

create table want as

select header from have1

union

select header from have2

order by header;

Valued Guide
Posts: 860

Re: Merge / Append two differnt columns into one column

Posted in reply to JUN_Sands

If I was doing this using datastep I would do the following:

data want;

set have1 have2;

run;

proc sort data=want nodup;by header;

Valued Guide
Posts: 860

Re: Merge / Append two differnt columns into one column

Posted in reply to JUN_Sands

also:

data want;

update have1 have2;

by header;

run;

Update can get a little trickier.  You might want to read up on that to get a better understanding if you use it.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 440 views
  • 6 likes
  • 4 in conversation