CODEWITHSUNDEEP
sqlsas
Sushant Shinde
Sushant Shinde

Reputation: 65

Adding column value in another table in sas

I have this table called A and it has id and have another table called B having blank id column.I want to add these id from table A to B .Suppose first it will add ID 1 in table B then ID 2 3 4 5 so on .. whenever i add value to table A it automatically gets add into the table B with time of 3 min .means table B will be refreshed by 3 min and fetch record from table A.But you have to check previous record value in table B and then add next value

--------------
|      ID      |
---------------
|       1      |
|       2      |
|       3      |
|       4      |
|       5      |
|       6      |
|       7      |
|       8      |
|       9      |
|       10     |
----------------

Upvotes: 2

Views: 1780

Answers (1)

momo1644
momo1644

Reputation: 1804

SAS data sets (tables) are not the same as Relational database tables so you can't add triggers on SAS tables. But you can schedule a sas program to run every 3 mins. to copy new values from table A to table B. Example code below.

Steps:

  1. Create data for tables A & B
  2. Create Lookup table with distinct IDs from B
  3. Insert in B new IDs which are not in Lookup table (SAS will have table lock if I was reading and inserting data into same table (B); that's why I have the lookup table)

Code:

data a;
input id;
datalines;
1
2
3
4
5
;
run;
data b;
input id;
datalines;
1
2
;
run;

proc sql;
create table lookup as select distinct id from b; quit;

proc sql;
 insert into work.b select * from a where a.id not in (select id from lookup) ;
quit;

Output: Table B post insert

id=1 
id=2 
id=3 
id=4 
id=5

Upvotes: 2

Related Questions