BookmarkSubscribeRSS Feed
littlestone
Fluorite | Level 6
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?
7 REPLIES 7
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Fluorite | Level 6
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
Super User
Look in the online help for UPDATE Statement. It has an example with a transaction data set which is your basic scenario.
Quentin
Super User
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.
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Ksharp
Super User
[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
Fluorite | Level 6
Thank you all !
FriedEgg
SAS Employee
data new;
merge main(in=b)
support(in=a);
by id index;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4177 views
  • 0 likes
  • 6 in conversation