07-16-2014 12:32 PM
I have a table that I create daily using proc sql. Currently the last step in my SAS code drops the previous version of the table and creates the table new again with the additional data that has been collected over the day. The problem is that this resets the permissions on that table name for users that query it.
How would I use proc sql to update/insert into the table the new records or changed records. Each record does have a unique ID. I would have access to the new and the old table.
07-16-2014 03:43 PM
Only thing is I want to insert only the new rows is there away to delete the old rows and keep the table i.e. delete all the rows and add the rows back from the most recent query?
07-16-2014 03:48 PM
Then you're replacing the table, not really adding new rows. SAS will automatically overwrite the table without warning you, unless you've changed some settings somewhere.
create table master as
select * from new_data;
07-16-2014 04:08 PM
Surely it is possible to update without replacing the sas table, like
proc sql ;
delete from table where key in( select distinct key from transactions );
Insert into table select * from transactions ;
To make it work this simply requires transactions to have the same structure( column type and order) as the master table, a unique key column i have assumed called key and space wouldn't be wasted if the master table is created with dataset options
compress =yes reuse= yes
07-17-2014 09:14 AM
Yes this would work and is what I did yesterday except I needed to write the table to another database with different credentials and connection strings. I tried to save the table to a library I created in the program too but when I ran the batch file to run the program it didn't update the table.
07-17-2014 09:57 AM
Depending on the requirements, you could use the update statement in a datastep;
Or you could update via SQL, though its a bit verbose:
update table BASE
set VAR1=(select THIS.VAR1 from NEW_DATA THIS where THIS.ID=BASE.ID),
Note you could also generate this from a call execute to save typing but that's another topic.
07-16-2014 09:49 PM
As Reeza said "you're replacing the table, not really adding new rows". Your real problem is as you said in your original post "that this resets the permissions on that table name for users that query it". So, why not address this directly, rather creating a potentially expensive and confusing work-around?
Using the FILENAME, FOPEN, FOPTNUM and FINFO functions (in a datastep) you can determine the current access permissions - which can be saved into macro variables. (the details are dependent your OS etc.)
Then run your existing code that "creates the table new again".
Then use the SYSTEM function (or an X statement) to set access permissions with e.g. the CHMOD command.