SAS DIS - Data steps in user written code

[May15]

Hi all,

I have a doubt regarding the properties of a table after running a particular data step in the user written code trasnformation of SAS Data Integration Studio.

Supposing that table A is a table created in SAS Data Integration Studio with primary key, indexes, constraints, columns with specific formats and informats,... Does anyone know if we lose these properties after running a step like this?

data A;
set A;
if <condition>;
run;

Thanks,

LF

[Patrick · Accepted solution]

@May15

The code you've posted will create a new table with a name as defined via "DATA A;". 

The table in the SET statement will get used for defining the variables for this new table. Indices, constraints and other table attributes won't get replicated.

During data step execution output records will get written to a table with a temporary name (automatically created by SAS).

After successful completion of all data step iterations the table will get renamed to the name as defined in the DATA statement  - "A" in your case. This renaming process will replace any already pre-existing same named table with the newly created table (different physical object).

Because any indices and constraints belong to the old same named table, these indices and constraints will get dropped as well.

Using DIS: 

Best practice is to use the available transformations as far as possible and to avoid User Written Code. To subset data use the SQL Extract or SQL Delete transformation.

If you really must use User Written Coden then try to incorporate the available macro variables created by the transformation so your code remains at least to a certain degree metadata driven.

[May15]

Hi Patrick,

Many thanks for your answer. It was really helpful.

[May15]

Hi Patrick,

Regarding your answer, a question have came up:

I've tried to use the Delete transformation. However, we don't save space using it. Am I right? It is an issue to me.

Thanks,

LF15

[May15]

Hi @Patrick,

Maybe it is a redundant question but it has come up: Even if we create a data set with a different name from the one that is in the "set" instruction, we also lose the indexes and constraints information from A, right? I mean:

data B;
set A;
if <condition>;
run;

Thanks,

May15

[LinusH]

@May15, since this is a new question please create a new thread in the future.

Regarding delete you might be right, given that your target table is a SAS data set. When you delete rows "in place" the space is not physically freed. What you can do is definte the tbke with the REUSE dat set option. Another option is to recreate the table with some frequency.

[May15]

Hi LinusH,

Thanks. Just one doubt: What does tbke stand for?

[LinusH]

Oh darn auto correct...😝
It should read "...define the table with the REUSE data set option."

[May15]

Hi LinusH,

Oh ok, now it makes sense

Thanks!