create new table based on existing tables

[littlestone]

I apologize for posting this question as I just posted a similar one. However I just couldn't figure out how to do it.

Suppose I have two tables: Main and Support. The Main table has MANY variables and may look like this:

Main:

ID......A......B......C......D......E......F......Index
001....0......0.......0.......0......0......0.......1
001....0......0.......0.......0......0......0.......2
001....0......0.......0.......0......0......0.......3
002....0......0.......0.......0......0......0.......4
002....0......0.......0.......0......0......0.......5
003....0......0.......0.......0......0......0.......6
003....0......0.......0.......0......0......0.......7
004....0......0.......0.......0......0......0.......8
005....0......0.......0.......0......0......0.......9

And Support table look like this:

Support:

ID......E......Index
001....1.......3
003....1.......6
005....1.......9

Now, based on these two tables, I want to create a new table called New which is
actually an updated Main (i.e., the E variable in Main is updated by the E
variable in Support if both ID and Index are matched). So the New table will look like:

New:

ID......A......B......C......D......E......F......Index
001....0......0.......0.......0......0......0.......1
001....0......0.......0.......0......0......0.......2
001....0......0.......0.......0......1......0.......3
002....0......0.......0.......0......0......0.......4
002....0......0.......0.......0......0......0.......5
003....0......0.......0.......0......1......0.......6
003....0......0.......0.......0......0......0.......7
004....0......0.......0.......0......0......0.......8
005....0......0.......0.......0......1......0.......9


What kind of SAS Data Step or Proc SQL can accomplish such task?

[SPR]

Hello Littlestone,

This ia a solution:
[pre]
proc SQL;
create table new as
select a.ID, a.B, a.C, a.D,
case
when b.E NE . then b.E
else a.E
end as E, a.F, a.Index
from main as a left join support as b on a.ID=b.ID and a.INDEX=b.INDEX
;quit;
[/pre]
Sincerely,
SPR

[littlestone]

SPR, thank you very much.

In your codes, every variable in Main has to be selected

...
select a.ID, a.B, a.C, a.D, ..., a.F, a.Index
.....

Do you think there could be an alternative method that does not require to list every variable? a.*?

As you know, in some bioinformatics data, there could be tens or even a hundred variables, listing each of them in the code is kind of inconvenience. I have tried to use a.*, but could not make it work the right way.

[ballardw]

Look in the online help for UPDATE Statement. It has an example with a transaction data set which is your basic scenario.

[Quentin]

Hi,

I think you could use a.* if you don't mind renaming variables. I'm still learning SQL, so may be mistaken, but i think below should work:

proc SQL;
create table new (drop=E rename=(NewE=E)) as
select a.*, coalesce(b.E,a.E) as newE
from main as a left join support as b on a.ID=b.ID and a.INDEX=b.INDEX
;quit;

Or you can use macros to generate the lists for the select statement. The macro language is a grea list maker....

--Q.

[Ksharp]

[pre]
data temp;
infile datalines dlm='.';
input id $ a b c d e f index;
datalines;
001....0......0.......0.......0......0......0.......1
001....0......0.......0.......0......0......0.......2
001....0......0.......0.......0......0......0.......3
002....0......0.......0.......0......0......0.......4
002....0......0.......0.......0......0......0.......5
003....0......0.......0.......0......0......0.......6
003....0......0.......0.......0......0......0.......7
004....0......0.......0.......0......0......0.......8
005....0......0.......0.......0......0......0.......9
;
run;
data index;
infile datalines dlm='.';
input id $ e index;
datalines;
001....1.......3
003....1.......6
005....1.......9
;
run;
[/pre]

proc sql;

 update temp as _a

   set e=(select e from index as _b

       where _b.id eq _a.id and _b.index
eq _a.index)

    where id eq (select id from index as
_b

       where _b.id eq _a.id ) and index eq (select index from index as _b

                                               
where _b.index eq _a.index)

    ;

quit;

Ksharp

[littlestone]

Thank you all !

[FriedEgg]

data new;
merge main(in=b)
support(in=a);
by id index;
run;