CODEWITHSUNDEEP
sqlsasdatabase-performance
user667489
user667489

Reputation: 9569

Can I modify an existing SAS dataset without creating a temporary file?

Apart from the modify statement, are there any other ways of modifying the contents of SAS datasets (i.e. altering values, or adding or removing rows or columns) that don't involve creating a temporary file in work and then replacing the entire original file?

Related question: if I have a single proc sql with one create table statement and several insert statements, all targeting the same table, will SAS end up overwriting the output table several times during execution, or is it clever enough to do all the writes in one pass? Let's assume that I'm not connecting to any other DBMS.

Since 2 people have posted this already, the following is not a valid answer:

data lib.dsn;
  set lib.dsn;
  /*Insert logic here*/
run;

If you do this, SAS creates a temporary file and replaces the original lib.dsn once the data step is complete. If you interrupt this kind of data step, there will be an error in the log, but the original dataset will remain unchanged.

Upvotes: 3

Views: 1972

Answers (4)

vasja
vasja

Reputation: 4792

Update rows with PROC SQL; UPDATE

delete with PROC SQL; DELETE

add with PROC APPEND or PROC SQL; INSERT

Upvotes: 1

JJFord3
JJFord3

Reputation: 1985

Original Answer: Adding/removing columns or adding/removing rows can all be done with a data step.

The drop statement removes the variable original_variable_A from the dataset. The line "new_variable = 25;" adds a new variable to the dataset. The do loop adds new rows. The where clause removes any rows not satisfying the condition outlined.

data libname.permanent_data;
    set libname.permanent_data;
    drop original_variable_A;
    new_variable = 25;
    do i = 1 to 2;
        original_variable_B = 3;
        new_variable = 2;
        output;
    end;
    where original_variable_B <= 50;
run;

Revised Answer: I think there may be come confusion in the meaning of "temporary file". If by temporary file you mean dataset in work directory, my original answer will suffice. However, if you mean the standard SAS dataset creation of permanent files as explained in your comment... I think you can do some manipulation with proc datasets on views that will certainly not create the temp files. https://support.sas.com/rnd/base/Tipsheet_DATASETS.pdf

Upvotes: -1

dannmate
dannmate

Reputation: 106

Yes. Say if I have a dataset in the location 'C:\Temp' that contains customer addresses called customer_addr. All you have to do is reference the same library and dataset in the data step and it will overwrite the existing dataset instead of putting it to the Work library.

libname Customers 'C:\Temp';
data Customers.customer_addr;
set Customers.customer_addr;
*do some logic here to remove or filter rows/columns;
run;

Upvotes: -1

user667489
user667489

Reputation: 9569

I found one - but are there other similar methods for overwriting rows, or adding/removing variables? From the help page for the append statement:

The APPEND statement bypasses the processing of data in the original data set and adds new observations directly to the end of the original data set.

Found another - it seems that the remove statement can delete rows in the way I want to, but only if I'm using a modify statement, which I already knew about.

Upvotes: 0

Related Questions