Help using Base SAS procedures

create new table based on existing tables

Reply
Frequent Contributor
Posts: 89

create new table based on existing tables

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?
Super Contributor
Super Contributor
Posts: 365

Re: create new table based on existing tables

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
Frequent Contributor
Posts: 89

Re: create new table based on existing tables

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.
Super User
Posts: 10,516

Re: create new table based on existing tables

Look in the online help for UPDATE Statement. It has an example with a transaction data set which is your basic scenario.
PROC Star
Posts: 1,233

Re: create new table based on existing tables

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.
Super User
Posts: 9,682

Re: create new table based on existing tables

[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



Frequent Contributor
Posts: 89

Re: create new table based on existing tables

Thank you all !
Trusted Advisor
Posts: 1,300

Re: create new table based on existing tables

data new;
merge main(in=b)
support(in=a);
by id index;
run;
Ask a Question
Discussion stats
  • 7 replies
  • 199 views
  • 0 likes
  • 6 in conversation