SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS DIS - Data steps in user written code

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

SAS DIS - Data steps in user written code

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


Accepted Solutions
Solution
‎09-30-2017 01:04 PM
Respected Advisor
Posts: 4,274

Re: SAS DIS - Data steps in user written code

[ Edited ]

@LF15

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.

View solution in original post


All Replies
Solution
‎09-30-2017 01:04 PM
Respected Advisor
Posts: 4,274

Re: SAS DIS - Data steps in user written code

[ Edited ]

@LF15

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.

Highlighted
Contributor
Posts: 37

Re: SAS DIS - Data steps in user written code

Hi Patrick,

 

Many thanks for your answer. It was really helpful.

Contributor
Posts: 37

Re: SAS DIS - Data steps in user written code

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

 

Contributor
Posts: 37

Re: SAS DIS - Data steps in user written code

[ Edited ]

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

Super User
Posts: 5,607

Re: SAS DIS - Data steps in user written code

@LF15, 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.
Data never sleeps
Contributor
Posts: 37

Re: SAS DIS - Data steps in user written code

Hi LinusH,

 

 

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

 

 

 

Super User
Posts: 5,607

Re: SAS DIS - Data steps in user written code

Oh darn auto correct...😝
It should read "...define the table with the REUSE data set option."
Data never sleeps
Contributor
Posts: 37

Re: SAS DIS - Data steps in user written code

Hi LinusH,

 

Oh ok, now it makes sense Smiley Happy

 

Thanks!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 601 views
  • 3 likes
  • 3 in conversation