topic Re: create new table based on existing tables in SAS Procedures

create new table based on existing tables

littlestone — Wed, 23 Mar 2011 18:33:42 GMT

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?

Re: create new table based on existing tables

SPR — Wed, 23 Mar 2011 19:23:31 GMT

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

Re: create new table based on existing tables

littlestone — Wed, 23 Mar 2011 20:26:39 GMT

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.

Re: create new table based on existing tables

ballardw — Wed, 23 Mar 2011 22:56:04 GMT

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

Re: create new table based on existing tables

Quentin — Thu, 24 Mar 2011 00:22:20 GMT

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.

Re: create new table based on existing tables

Ksharp — Thu, 24 Mar 2011 03:03:29 GMT

[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

Re: create new table based on existing tables

littlestone — Thu, 24 Mar 2011 18:22:33 GMT

Thank you all !

Re: create new table based on existing tables

FriedEgg — Thu, 24 Mar 2011 19:37:02 GMT

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