05-01-2017 01:48 PM - edited 05-01-2017 01:49 PM
How to update table A by table B with proc sql?(after update, A should has 100 rows)
do a=1 to 50;
b=int(ranuni( 12345 )*1000000);
do a=1 to 100;
b=int(ranuni( 54321 )*1100000);
05-01-2017 02:22 PM - edited 05-01-2017 02:24 PM
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;
05-01-2017 03:53 PM
05-01-2017 03:57 PM - edited 05-01-2017 03:58 PM
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?
05-01-2017 04:02 PM - edited 05-01-2017 04:04 PM
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
05-01-2017 04:32 PM
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) ;
05-01-2017 08:54 PM
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;
05-01-2017 04:01 PM
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;
05-01-2017 04:09 PM
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.
05-02-2017 08:04 AM
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:
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.
05-02-2017 11:50 AM