proc sql add new rows

Reply
Contributor
Posts: 23

proc sql add new rows

Hi Community,

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.

Regards,

Super User
Posts: 17,836

Re: proc sql add new rows

Why not use proc append though?

proc append base=master data=new_data;

run;

SQL version:

proc sql;

insert into master

select * from new_data;

quit;

Contributor
Posts: 23

Re: proc sql add new rows

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?

Super User
Posts: 17,836

Re: proc sql add new rows

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.

Proc sql;

create table master as

select * from new_data;

quit;

OR

Data master;

set new_records;

run;

Valued Guide
Posts: 2,175

Re: proc sql add new rows

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 ;

quit ;

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

good luck

peterC

Contributor
Posts: 23

Re: proc sql add new rows

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.

Contributor
Posts: 23

Re: proc sql add new rows

Sorry,  It actually worked I just had to wait a little longer on the refresh.  Both Dave and Peters work but what I did was closer to Peters.

Super User
Super User
Posts: 7,401

Re: proc sql add new rows

Depending on the requirements, you could use the update statement in a datastep;

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001329151.htm

Or you could update via SQL, though its a bit verbose:

proc sql;

     update table BASE

     set     VAR1=(select THIS.VAR1 from NEW_DATA THIS where THIS.ID=BASE.ID),

               VAR2=(...);

quit;

Note you could also generate this from a call execute to save typing but that's another topic.

Contributor
Posts: 25

Re: proc sql add new rows

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.

Good luck,

Dave

Ask a Question
Discussion stats
  • 8 replies
  • 1531 views
  • 0 likes
  • 5 in conversation