DATA Step, Macro, Functions and more

How to append the data from two datasets vertically in SAS?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

How to append the data from two datasets vertically in SAS?

I have datasets like

 

Obs

Num1

Num2

Num3

 1

1

.

1

2

2

5

2

3

3

5

.

4

4

5

4

5

6

7

8

 

 

Num6

Num7

 

3

4

 

4

2

 

6

.

 

2

6

   

 Output should be

Obs Num1 Num2 Num3 Num6 Num7 1 2 3 4 5
1.134
25242
35.6.
45426
678

Accepted Solutions
Solution
‎01-12-2017 01:19 PM
Super User
Posts: 17,780

Re: How to append the data from two datasets vertically in SAS?

@HB This is incorrect you can merge without a BY statement if you're doing a side by side merge.

 

data want;
merge file1 file2;
run;

proc print data=want;run;

 

 

SAS Output

The SAS System

Obs obs var1 var2 var3 var4 var5
1 1 1 . 1 3 4
2 2 2 5 2 4 2
3 3 3 5 . 6 .
4 4 4 5 4 2 6
5 5 6 7 8 . .

View solution in original post


All Replies
Trusted Advisor
Posts: 1,369

Re: How to append the data from two datasets vertically in SAS?

just do:

 

data want;

merge table1 table2;

by;

run;

Occasional Contributor
Posts: 18

Re: How to append the data from two datasets vertically in SAS?

If I do merge data will be appended after the first dataset, but I want it along with the first dataset

Trusted Advisor
Posts: 1,369

Re: How to append the data from two datasets vertically in SAS?

merge will do what you expect. Just try it and check result.
Super User
Posts: 17,780

Re: How to append the data from two datasets vertically in SAS?


ujwala wrote:

If I do merge data will be appended after the first dataset, but I want it along with the first dataset


Try it. If it doesn't work, post the code and log. The premise is correct, so if it doesn't work, either the situation is not as described or your code is incorrect.

 

 

Frequent Contributor
Frequent Contributor
Posts: 89

Re: How to append the data from two datasets vertically in SAS?

Your data is going to need a key to be merged with.

 

I'll assume a variable called obs (which is a bad variable name!)

 

Doing this:

data file1;
	input obs var1 var2 var3;
	datalines;
1 1 . 1
2 2 5 2
3 3 5 .
4 4 5 4
5 6 7 8
;

data file2;
	input obs var4 var5;
	datalines;
1 3 4 
2 4 2
3 6 .
4 2 6
;

proc sort data=file1 out=file1_sorted;
	by obs;
run;

proc sort data=file2 out= file2_sorted;
	by obs;
run;

* the (in = a) and (if a) function like a left join;
data combined;
	merge file1_sorted (in = a) file2_sorted;
	by obs; 
	if a;
run;

gives us this:

obs	var1	var2	var3	var4	var5
1	1		1	3	4
2	2	5	2	4	2
3	3	5		6	
4	4	5	4	2	6
5	6	7	8		

HTH.

Solution
‎01-12-2017 01:19 PM
Super User
Posts: 17,780

Re: How to append the data from two datasets vertically in SAS?

@HB This is incorrect you can merge without a BY statement if you're doing a side by side merge.

 

data want;
merge file1 file2;
run;

proc print data=want;run;

 

 

SAS Output

The SAS System

Obs obs var1 var2 var3 var4 var5
1 1 1 . 1 3 4
2 2 2 5 2 4 2
3 3 3 5 . 6 .
4 4 4 5 4 2 6
5 5 6 7 8 . .
Frequent Contributor
Frequent Contributor
Posts: 89

Re: How to append the data from two datasets vertically in SAS?

[ Edited ]

@Reeza I don't consider it incorrect. It may be less terse, but the verbosity helps me remember all the components to the statement.

 

It's also working code.

Super User
Posts: 17,780

Re: How to append the data from two datasets vertically in SAS?


HB wrote:

Your data is going to need a key to be merged with.

 

 


@HB the statement above is what I would consider incorrect. No key is needed in this situation, you can add one, but it's not required. Your code/solution does work, and in that respect is correct. 

Frequent Contributor
Frequent Contributor
Posts: 89

Re: How to append the data from two datasets vertically in SAS?

Without a key in the original data, there is an assumption of order. Without that implied order, which might be a problem down the line in the real world, knowing how the first dataset relates to the second dataset becomes extremely problematic. As they are, they may be able to be smashed together. Get them out of order and a key will be needed. In my opinion, it's better to set it up with a key and use a key. YMMV. You're right.
Super User
Posts: 5,079

Re: How to append the data from two datasets vertically in SAS?

If you know that your first data set is the one with more observations, you could also code it this way:

 

data want;

set have1;

if done=0 then set have2 end=done;

output;

call missing (of _all_);

run;

 

Contributor
Posts: 52

Re: How to append the data from two datasets vertically in SAS?

 

Just merge them, without any by statement.

 

data want;
merge data1 data2;
run;

 


Cheers from India!

Manjeet
Respected Advisor
Posts: 3,887

Re: How to append the data from two datasets vertically in SAS?

@ujwala

May be after all the discussion in this tread you also want to have a read of the docu to get a bit more insight of the options.

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p15jvywi5avt3cn1bee8...

 

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 246 views
  • 0 likes
  • 7 in conversation