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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.