Merge two variables from two tables

[nicnad]

Hi

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.

[Reeza · Accepted solution]

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;

[nicnad]

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

[Reeza]

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;

[nicnad]

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.

[ArtC]

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;

[nicnad]

Thank you all for your replies!