use proc SQL to update whole column from another SAS dataset

Reply
Regular Contributor
Posts: 236

use proc SQL to update whole column from another SAS dataset

[ Edited ]

Hello all,

 

How to update table A by table B with proc sql?(after update, A should has 100 rows)

 

Thanks!

 

data A;
do a=1 to 50;
b=int(ranuni( 12345 )*1000000);
output;
end;
run;

data B;
do a=1 to 100;
b=int(ranuni( 54321 )*1100000);
output;
end;
run;

Contributor
Posts: 57

Re: use proc SQL to update whole column from another SAS dataset

[ Edited ]

For pedagogical purposes, I changed the name of the tables and columns, but you'll find this code easier to understand (and others too, I hope).

 

This will work.  Unfortunatley, you can't use the update procedures similar to the ones found in SQL Server, Oracle, and other populare database programs.  This is a bit of a workaround.  I hope this helps.

data Table1;
do cola=1 to 50;
colb=int(ranuni( 12345 )*1000000);
output;
end;
run;

data Table2;
do cola=1 to 100;
colb=int(ranuni( 54321 )*1100000);
output;
end;
run;

proc sql;
update work.Table1
set
colb=(select colb from work.Table2 where Table1.cola=Table2.cola);
quit;

 

If you simply want to add the rows from Table 1 to Table 2, use the followings:

proc sql;
insert into Table1
select * from
Table2;
quit;
Regular Contributor
Posts: 236

Re: use proc SQL to update whole column from another SAS dataset

Thank you,
First method will update only 50 rows,
second method will append table2 to table1.
While I want table1 to be updated exactly same as table2. what I can do then?

Thanks!
Contributor
Posts: 57

Re: use proc SQL to update whole column from another SAS dataset

[ Edited ]

Then it's not clear what you are wanting to do.  Can you provide an example using the first few values of Table1 and Table 2?

Regular Contributor
Posts: 236

Re: use proc SQL to update whole column from another SAS dataset

[ Edited ]

Table1 is old data with only 50 rows.
table2 is new data. will has more rows(let's say 100 rows).
I want to create proc sql to update table1 using table2 as a lookup table.after update. table1 will be exact same as table2. it has 100 rows

Super User
Super User
Posts: 6,842

Re: use proc SQL to update whole column from another SAS dataset

Make separate update and insert steps?

update table1 
set colb = (select colb from table2 where table1.cola=table2.colb)
where cola in (select cola from table2)
;
insert into table1
select * from table2 
where cola not in (select cola from table1)
;

 

 

Contributor
Posts: 57

Re: use proc SQL to update whole column from another SAS dataset

There are two ways that are easily accessible (without writing your own look-up table function) that should get you what you need.  However, the first makes multiple sql statements, and the second isn't really an update, but it should accomplish what you need.

This first option is a combination of the methods I already presented.  First, you update existing rows and then you have to insert rows that do not already exist in Table 1.  To accomplish this, you have to save your inital data to a temporary datasets (in this case work.temp):

proc sql;
update work.Table1
set
colb=(select colb from work.Table2 where Table1.cola=Table2.cola);
quit;

/*Create temporary table*/
proc sql;
create table work.temp as
select * from
Table1;
quit;

proc sql;
insert into Table1 
select * from
Table2
where
Table2.cola not in(select cola from temp);
quit;


The second appraoch does not update the table, but instead creates a new one (that can be presumably used to overwrite your existing data).  This appraoch performs a full out join on both tables and attempts to use any matching Colb record in table2 first.  if one is not found, it uses the record from Table1.  If no records are found, but it's a new records, the new record will be created.

proc sql;
create table work.Table1 as
select
coalesce(b.colb, a.colb)
from
work.Table1 a FULL OUTER JOIN
work.Table2 b on
a.Cola=b.Cola;
quit;
Super User
Super User
Posts: 6,842

Re: use proc SQL to update whole column from another SAS dataset

If you want to make table1 be a copy a table2?

create table1 as select * from table2;

If you really want to join two tables of DIFFERENT sizes then I wouldn't use SQL.

data want ;
  merge table1 table2 ;
  by id_var ;
run;
Regular Contributor
Posts: 236

Re: use proc SQL to update whole column from another SAS dataset

I have to use sql. I need to update an excel spreadsheet. which has lots of version limitation.
Super User
Super User
Posts: 6,842

Re: use proc SQL to update whole column from another SAS dataset

Not really a good idea to update an Excel spreadsheet. Excel is really not a database.

If you are forced to use Excel then keep the data sheet separate from the presentation sheet that needs formatting.

Respected Advisor
Posts: 4,131

Re: use proc SQL to update whole column from another SAS dataset

The only two ways I know which allow you to actually update/refresh a range/cells in an Excel sheet and not fully replace it are:

1. DDE

2. SAS AMO

 

DDE is quite an outdated technique and even though it gives you the best "modular" access to Excel I can't recommend to use it as it locks you into a "problematic" very old-fashioned way of interacting with Excel (and you'll get into huge problems the day you need to migrate into a client-server environment).

 

SAS AMO: That could do the job nicely. You still couldn't fully automate the process though.

 

...and that's why I totally agree with what @Tom proposes. That's the way which will cause you the least pain and which is the most future proof and will work in any SAS architecture.

Regular Contributor
Posts: 236

Re: use proc SQL to update whole column from another SAS dataset

Thank you for the reconfirm!
Maybe I will try DDE later, this will pain only on my computer,and the excel file from it will be fine for anyone.
Contributor
Posts: 57

Re: use proc SQL to update whole column from another SAS dataset

can't you update data using an OLE or ODBC connection to an Excel sheet or not?

Ask a Question
Discussion stats
  • 12 replies
  • 225 views
  • 6 likes
  • 4 in conversation