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.
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;
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;
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
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;
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.
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;
Thank you all for your replies!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.