turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- SQL Update - Updating a table with corresponding v...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-03-2011 08:38 AM

I have 2 tables and I'm wanting to update the values in the first with the corresponding values in the second, e.g.

table1

a 1

b 2

c 3

d 4

table2

b 5

c 6

to result in;

table1

a 1

b 5

c 6

d 4

I'm not that familiar with SAS SQL syntax and the following code I wrote is displaying an error.

PROC SQL ;

UPDATE

table1 AS a

LEFT JOIN

table2 AS b

ON

a.var1 EQ b.var1

SET

a.var2 = b.var2

;

QUIT ;

65 LEFT JOIN

----

79

76

ERROR 79-322: Expecting a SET.

ERROR 76-322: Syntax error, statement will be ignored.

Does SAS SQL support this kind of updating?

As always, any help with this is greatly appreciated.

Cheers,

Fat Captain.

table1

a 1

b 2

c 3

d 4

table2

b 5

c 6

to result in;

table1

a 1

b 5

c 6

d 4

I'm not that familiar with SAS SQL syntax and the following code I wrote is displaying an error.

PROC SQL ;

UPDATE

table1 AS a

LEFT JOIN

table2 AS b

ON

a.var1 EQ b.var1

SET

a.var2 = b.var2

;

QUIT ;

65 LEFT JOIN

----

79

76

ERROR 79-322: Expecting a SET.

ERROR 76-322: Syntax error, statement will be ignored.

Does SAS SQL support this kind of updating?

As always, any help with this is greatly appreciated.

Cheers,

Fat Captain.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FatCaptain

03-03-2011 09:29 AM

One of many ways.

[pre]

data table1;

infile cards;

input var1 $ var2;

cards;

a 1

b 2

c 3

d 4

;

data table2;

infile cards;

input var1 $ var2;

cards;

b 5

c 6

;

proc sql noprint;

create table table1 as select a.var1, coalesce(b.var2,a.var2) as var2

from table1 as a left join table2 as b on a.var1=b.var1;

quit;

[/pre]

[pre]

data table1;

infile cards;

input var1 $ var2;

cards;

a 1

b 2

c 3

d 4

;

data table2;

infile cards;

input var1 $ var2;

cards;

b 5

c 6

;

proc sql noprint;

create table table1 as select a.var1, coalesce(b.var2,a.var2) as var2

from table1 as a left join table2 as b on a.var1=b.var1;

quit;

[/pre]

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FatCaptain

03-03-2011 09:37 AM

Another way.. Make sure to sort the two tables before merge .. but for this case they are already sorted so no sorting is done..

data table1;

infile cards;

input var1 $ var2 @@;

cards;

a 1 b 2 c 3 d 4

;

data table2;

infile cards;

input var1 $ var2 @@;

cards;

b 5 c 6

;

data x;

merge table1 table2;

by var1;

run;

data table1;

infile cards;

input var1 $ var2 @@;

cards;

a 1 b 2 c 3 d 4

;

data table2;

infile cards;

input var1 $ var2 @@;

cards;

b 5 c 6

;

data x;

merge table1 table2;

by var1;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SAS83

03-03-2011 09:43 AM

Make sure Table1 is first then Table2 in the merge statement.. if you dont use ina and inb....

data table1;

infile cards;

input var1 $ var2 @@;

cards;

a 1 b 2 c 3 d 4

;

data table2;

infile cards;

input var1 $ var2 @@;

cards;

b 5 c 6

;

data x;

merge table1 (in=ina) table2 (in=inb);

by var1;

if ina;

run;

this is similar to SQL left join..

data table1;

infile cards;

input var1 $ var2 @@;

cards;

a 1 b 2 c 3 d 4

;

data table2;

infile cards;

input var1 $ var2 @@;

cards;

b 5 c 6

;

data x;

merge table1 (in=ina) table2 (in=inb);

by var1;

if ina;

run;

this is similar to SQL left join..

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FatCaptain

03-03-2011 07:35 PM

Here's a sql solution since all the other are datastep and its what you were looking for initially. I doubt its more efficient, but useful to know.

[pre]

data have1;

input var1 $1 var2;

datalines;

a 1

b 2

c 3

d 4

;

run;

data have2;

input var1 $1 var2;

datalines;

b 5

c 6

;

run;

proc sql;

update have1 as h1

set var2=(select var2 from have2 as h2 where h1.var1=h2.var1)

where var1 in (select var1 from have2);

quit;

[/pre]

[pre]

data have1;

input var1 $1 var2;

datalines;

a 1

b 2

c 3

d 4

;

run;

data have2;

input var1 $1 var2;

datalines;

b 5

c 6

;

run;

proc sql;

update have1 as h1

set var2=(select var2 from have2 as h2 where h1.var1=h2.var1)

where var1 in (select var1 from have2);

quit;

[/pre]

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

10-31-2016 11:56 AM - edited 10-31-2016 11:57 AM

Reeza provides the ideal solution, eliminating the need to create a new data set.

proc sql; update have1 as h1 set var2=(select var2 from have2 as h2 where h1.var1=h2.var1)where var1 in (select var1 from have2);quit;

I reckon the where statement only improves performance. If you're updating only a few values in a very large data set then this will significantly speed up the update. Definitely my favorite solution to the problem.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FatCaptain

03-04-2011 06:25 AM

Thank you all for your responses.

Cheers,

Fat Captain.

Cheers,

Fat Captain.