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

use proc SQL to update whole column from another SAS dataset

GeorgeSAS — Mon, 01 May 2017 17:49:30 GMT

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;

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

statistician13 — Mon, 01 May 2017 18:24:29 GMT

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;

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

GeorgeSAS — Mon, 01 May 2017 19:53:55 GMT

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!

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

statistician13 — Mon, 01 May 2017 19:58:51 GMT

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?

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

Tom — Mon, 01 May 2017 20:01:12 GMT

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;

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

GeorgeSAS — Mon, 01 May 2017 20:04:03 GMT

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

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

GeorgeSAS — Mon, 01 May 2017 20:03:32 GMT

I have to use sql. I need to update an excel spreadsheet. which has lots of version limitation.

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

Tom — Mon, 01 May 2017 20:09:28 GMT

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.

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

Tom — Mon, 01 May 2017 20:32:29 GMT

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)
;

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

statistician13 — Tue, 02 May 2017 00:54:39 GMT

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;

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

Patrick — Tue, 02 May 2017 12:04:42 GMT

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.

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

GeorgeSAS — Tue, 02 May 2017 15:50:38 GMT

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.

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

statistician13 — Tue, 02 May 2017 17:13:26 GMT

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