topic Re: Merge two variables from two tables in SAS Procedures

Merge two variables from two tables

nicnad — Wed, 27 Feb 2013 21:11:15 GMT

Lets say I have the two following table :

Table : mylib.Test1

Name	Date	Var1	Var2
John	2012-01-01	aaa	bbb
Bob	2012-01-02	eee	ccc
Peter	2012-01-03	ffff	ddd

Table : Mylib.test2

Name	Date	Var9
Bob	2012-01-04	ttt
Fred	2012-01-05	yyy
Will	2012-01-05	vvv

I want to merge the two tables, so that I have all the names, dates, and the source table dates (and if name should be repeated, have the multiple entries) like this :

Name	Date	Source_Table
John	2012-01-01	test1
Bob	2012-01-02	test1
Peter	2012-01-03	test1
Bob	2012-01-04	test2
Fred	2012-01-05	test2
Will	2012-01-05	test2

Thank you very much for your help and time.

Re: Merge two variables from two tables

Reeza — Wed, 27 Feb 2013 21:21:39 GMT

If you don't need var1, var2 and var9 then

data want;

set mylib.test1 (keep=name date) mylib.test2 (keep=name date) indsname=source;

source_table=source;

run;

Re: Merge two variables from two tables

nicnad — Wed, 27 Feb 2013 22:13:48 GMT

Reeza thank you very much for this quick reply.

The set and keep procedure work well.

The only thing is that I am not able to make the part in bold work.

data want;

set mylib.test1 (keep=name date) mylib.test2 (keep=name date) indsname=source;

ERROR 22-322: Syntax error, expecting one of the following: END, KEY, KEYS, NOBS, POINT.

ERROR 76-322: Syntax error, statement will be ignored.

source_table=source;

run;

The error points on the equal sign between indsname=source

Re: Merge two variables from two tables

Reeza — Wed, 27 Feb 2013 22:37:26 GMT

That means you have SAS 9.1?

data want;

set mylib.test1 (keep=name date in=test1) mylib.test2 (keep=name date in=test2);

if test1 then source_table='test1';

else if test2 then source_table='test2';

run;

Re: Merge two variables from two tables

nicnad — Wed, 27 Feb 2013 23:00:20 GMT

This is exactly what I needed it works great.

One thing is that I am using the set procedure on 10 different table. Is there an another then writing 10 if statements?

Can I just state something like source_table = in?

Also I see that the length of my variable source_table is set to 5 (with the first value being test1 I guess) so when afterwards I use a longer string it is truncated to 5 chars only. How do I solve this%

Thank you very much for your help and time.

Re: Merge two variables from two tables

ArtC — Thu, 28 Feb 2013 06:42:00 GMT

Getting away from the IF-THEN/ELSE is why the INDSNAME= option was added to the SET statement.

You can declare the variable's length using the LENGTH statement. Right after the SET statement add something like:

length source_table $7;

Re: Merge two variables from two tables

nicnad — Thu, 28 Feb 2013 15:30:49 GMT

Thank you all for your replies!