BookmarkSubscribeRSS Feed
Egrodrigues2014
Fluorite | Level 6

Hi everyone,

 

When I set BULK LOAD option as active, SCD2 transformation create the following code (for ETLS_CLOSE as example):

 

      proc append base = EXT_VI."W6B3BTSM"n
         (
            BULKLOAD=YES
         )
         data = work."ETLS_CLOSE"n force; 
      run;

But this code doesn't work. I need to set "bl_psql_path='/PATH' " and "bl_delete_datafile=no" dataset option to BULKLOAD works properly.

I aware that I can change the automatic generated code to add these options.

 

Is there a way to configure these options at the transformation level? Or a way that I can add these options without change the code manually?

 

Thanks in advance.

2 REPLIES 2
LinusH
Tourmaline | Level 20

Unsure if you can do this using standard options in the transformation.

But I am wondering if you have a good use case here.

S in SCD stands for Slowly, so you shouldn't have millions of records that needs closing out.

If not done so already, you could try the SQL Pass through option, it might speed up the process depending on the performance of your RDBMS.

Data never sleeps
Egrodrigues2014
Fluorite | Level 6

Hi @LinusH ,

 

We need to load the historical records and this data volume will be only in the first iteration.

 

As you can see, we change the option "Upload library before targe table update" to EXT_VI library. But the "etls" tables still in work library.

 

 

/* Delete the work tables  */ 
      proc datasets lib=EXT_VI nolist nowarn memtype = (data view);
         delete "W6B3TJ5F"n;
         delete "W6B3TIUB"n;
         delete "W6B3TIOR"n;
      quit;   
     
         proc datasets lib=work nolist nowarn memtype = (data view);
            delete etls_newrcds;
            delete etls_match;
            delete etls_close;
         quit;

To load data from work to postgres tables, we need to set bulkload.

 

 

Is there a way so that these tables are also generated in the "EXT_VI" (postgres) library ? Thus, passthrough can be performed without the need for BULKLOAD.

 

We set "Libref to copy temporary work tables to" SCD option to EXT_VI, but it does not work. 

 

Thanks in advance.

SAS INNOVATE 2024

innovate-wordmarks-white-horiz.png

SAS is headed back to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team.

Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 456 views
  • 0 likes
  • 2 in conversation