Help using Base SAS procedures

Merge two variables from two tables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 186
Accepted Solution

Merge two variables from two tables


Hi

Lets say I have the two following table :

Table : mylib.Test1

NameDateVar1Var2
John2012-01-01aaabbb
Bob2012-01-02eeeccc
Peter2012-01-03ffffddd

Table : Mylib.test2

NameDateVar9
Bob2012-01-04ttt
Fred2012-01-05yyy
Will2012-01-05vvv

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 :

NameDateSource_Table
John2012-01-01test1
Bob2012-01-02test1
Peter2012-01-03test1
Bob2012-01-04test2
Fred2012-01-05test2
Will2012-01-05test2

Thank you very much for your help and time.


Accepted Solutions
Solution
‎02-27-2013 04:21 PM
Super User
Posts: 19,772

Re: Merge two variables from two tables

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;

View solution in original post


All Replies
Solution
‎02-27-2013 04:21 PM
Super User
Posts: 19,772

Re: Merge two variables from two tables

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;

Regular Contributor
Posts: 186

Re: Merge two variables from two tables

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

Super User
Posts: 19,772

Re: Merge two variables from two tables

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;

Regular Contributor
Posts: 186

Re: Merge two variables from two tables

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.

Valued Guide
Posts: 634

Re: Merge two variables from two tables

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;

Regular Contributor
Posts: 186

Re: Merge two variables from two tables

Thank you all for your replies!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 253 views
  • 6 likes
  • 3 in conversation