BookmarkSubscribeRSS Feed
GeorgeSAS
Lapis Lazuli | Level 10

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;

12 REPLIES 12
statistician13
Quartz | Level 8

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;
GeorgeSAS
Lapis Lazuli | Level 10
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!
statistician13
Quartz | Level 8

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?

GeorgeSAS
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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

 

 

statistician13
Quartz | Level 8

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;
Tom
Super User Tom
Super User

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;
GeorgeSAS
Lapis Lazuli | Level 10
I have to use sql. I need to update an excel spreadsheet. which has lots of version limitation.
Tom
Super User Tom
Super User

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.

Patrick
Opal | Level 21

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.

GeorgeSAS
Lapis Lazuli | Level 10
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.
statistician13
Quartz | Level 8

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 12 replies
  • 1664 views
  • 6 likes
  • 4 in conversation