topic Re: SAS DIS - Data steps in user written code in SAS Data Management

SAS DIS - Data steps in user written code

May15 — Fri, 29 Sep 2017 19:40:18 GMT

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,

Re: SAS DIS - Data steps in user written code

Patrick — Fri, 29 Sep 2017 22:04:54 GMT

@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.

Re: SAS DIS - Data steps in user written code

May15 — Sat, 30 Sep 2017 17:02:40 GMT

Hi Patrick,

Many thanks for your answer. It was really helpful.

Re: SAS DIS - Data steps in user written code

May15 — Fri, 15 Dec 2017 16:05:22 GMT

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

Re: SAS DIS - Data steps in user written code

LinusH — Fri, 15 Dec 2017 18:46:23 GMT

@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.

Re: SAS DIS - Data steps in user written code

May15 — Fri, 15 Dec 2017 22:00:18 GMT

Hi LinusH,

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

Re: SAS DIS - Data steps in user written code

LinusH — Sat, 16 Dec 2017 20:28:20 GMT

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

Re: SAS DIS - Data steps in user written code

May15 — Tue, 19 Dec 2017 23:57:38 GMT

Hi LinusH,

Oh ok, now it makes sense

Thanks!

Re: SAS DIS - Data steps in user written code

May15 — Tue, 16 Jan 2018 21:27:40 GMT

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