topic merge in SAS Enterprise Guide

merge

Lopes — Thu, 02 Jan 2014 20:20:58 GMT

How can I do a merge between 2 boards and take the values of Table 2 in case in Table 1 have in those places a specific value.

thank you very much

Maria

Re: merge

Fugue — Thu, 02 Jan 2014 20:45:41 GMT

Without having a sample of your data and expected output, I can only provide a general reply. There are several ways you could do this. Here are two options:

1. use a data step to merge the two datasets together, replacing the values in Table 1 with the values from Table 2 if the specified condition is met in Table 1. For this step to work, the data should be pre-sorted.

DATA together ;

merge table1 (in=a rename=(var1=table1var)) table2 (in=b rename=(var2=table2var));

by idno ; /* your by variables go here -- make sure tables are presorted */

if a and b then do ; /* change as needed to get only left side (if a) or right side (if b) or either side (if a or b), etc. */

if table1var = 999 then var1 = table2var;

else var1 = table1var ;

output;

end;

run;

2. use PROC SQL - no need to presort the data.

proc sql;

create table together as

select t1.idno

, ( case when t1.idno = 999 then t2.var1 else t1.var1 end ) as var1

from table1 as t1, table2 as t2

where t1.idno = t2.idno /* assumes you want an equijoin */

order by idno

quit;

These are only two approaches -- there are other methods that you could use.

Re: merge

Lopes — Thu, 02 Jan 2014 20:58:36 GMT

Thank you very much.

I have in the table1 25 variables in 6 of them I have the value 999999, in those cases I need to replace in the new table those values with the ones in table 2.

I was not able to attach the tables.

Have I explained better what I need?

In your example "IDNO" is the name of the table?

Re: merge

Fugue — Thu, 02 Jan 2014 22:41:34 GMT

In my example, I used IDNO as an example of the variable(s) that you would use to do the join. IDNO, in my example, is a fictional variable. Instead of IDNO, use the actual variable(s) that you are using to join the tables.

As I said, my response is general in nature since we don't have any sample data or expected output to work with. You don't need to attach the entire table(s), by the way. You can attach subsets of the data, or even samples with fictional data if you are concerned about privacy/confidentiality issues.

Regarding 6 variables having values of 999999, you can reuse the code samples I provided to recode as many variables as needed, eg

DATA together ;

merge table1 (in=a rename=(var1=table1var1 var2=table1var2 var3=table1var3 . . . etc . . . )) table2 (in=b rename=(var1=table2var1 var2=table2var2 var3=table2var3 . . . . etc . . . ));

by idno ; /* fictional join variable - to be replaced with your actual by variable(s) -- make sure tables are presorted */

if a and b then do ; /* change as needed to get only left side (if a) or right side (if b) or either side (if a or b), etc. */

if table1var1 = 999999 then var1 = table2var1;

else var1 = table1var1 ;

if table1var2 = 999999 then var2 = table2var2;

else var2 = table1var2 ;

if table1var3 = 999999 then var3 = table2var3;

else var3 = table1var3 ;

. . . .

more lines

. . . .

output;

end;

run;

OR in PROC SQL . . . .

proc sql;

create table together as

select t1.idno

, ( case when t1.var1 = 999999 then t2.var1 else t1.var1 end ) as var1

, ( case when t1.var2 = 999999 then t2.var2 else t1.var2 end ) as var2

, ( case when t1.var3 = 999999 then t2.var3 else t1.var3 end ) as var3

. . . . more case statements . . .

from table1 as t1, table2 as t2

where t1.idno = t2.idno /* fictional join variable. Assumes you want an equijoin */

order by idno

quit;

Re: merge

Lopes — Fri, 03 Jan 2014 12:37:00 GMT

Thank you very much.

I tried. The problema when using PROC SQL is that I in table 2 I only have the values with 999999 in table 1. And when doing this I need to have al the variables in table 2.

Another question, can I do this with more tan 2 tables? For instance take 3 variables from table 2, 5 from table 3, ...

Thanks!!

Re: merge

Fugue — Fri, 03 Jan 2014 19:08:29 GMT

You can use this general approach (either Data Step method or PROC SQL method) with more than 2 tables.

What do you mean by, ". . . I need to have all the variables in table 2"?

To help you any further, I think I need to see a sample of your input data and a sample of your desired output.