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

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

UPEN — Wed, 11 Jan 2017 21:44:41 GMT

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	.	1	3	4
2	5	2	4	2
3	5	.	6	.
4	5	4	2	6
6	7	8

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

Shmuel — Wed, 11 Jan 2017 21:53:08 GMT

just do:

data want;

merge table1 table2;

by;

run;

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

UPEN — Wed, 11 Jan 2017 21:57:37 GMT

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

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

Shmuel — Wed, 11 Jan 2017 22:09:27 GMT

merge will do what you expect. Just try it and check result.

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

Reeza — Wed, 11 Jan 2017 22:11:23 GMT

@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.

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

HB — Wed, 11 Jan 2017 22:13:23 GMT

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.

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

Reeza — Wed, 11 Jan 2017 22:18:01 GMT

@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	.	.

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

HB — Wed, 11 Jan 2017 22:29:39 GMT

@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.

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

Astounding — Wed, 11 Jan 2017 22:26:36 GMT

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;

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

Reeza — Wed, 11 Jan 2017 22:37:41 GMT

@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.

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

HB — Wed, 11 Jan 2017 22:43:20 GMT

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.

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

mnjtrana — Thu, 12 Jan 2017 06:52:14 GMT

Just merge them, without any by statement.

data want;
merge data1 data2;
run;

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

Patrick — Thu, 12 Jan 2017 06:59:35 GMT

@UPEN

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#p15jvywi5avt3cn1bee8r6c33ux1.htm