BookmarkSubscribeRSS Feed
sassharp
Calcite | Level 5

there is a 500M rows in RDBMS table. Eachday approximately 1000 new rows adds to the 500M rows. Out of 500M rows everyday 0 to 100 records updates  with the same old id one or two  field contents changes. Assume for 100 rows field contents changed. so now RDBMS contains 500M-100 +1000=500M and 900 rows.

Q) Need to replicate this RDBMS table as a SAS data set?

7 REPLIES 7
Doc_Duke
Rhodochrosite | Level 12

I'm not sure what the issue is.  If "replicate" means "copy", then a DATA/SET combination will do it.  If you just want to access the data, a LIBNAME to the RDBMS will do that.  If you want to replicate the "process" of updating the RDBMS table using SAS code, you've got a lot of work to do, particularly if you are thinking about in-place replacement.

sassharp
Calcite | Level 5


sorry not to mention Doc

Doc.

Lawrence Muhlbaier

shivas
Pyrite | Level 9

Hi,

Are you looking for something like SCD type 2 Loader/Type 1 columns with version number.This can achieved using SCD type 2 transformation.

Thanks,

Shiva

sassharp
Calcite | Level 5

@Shivas

To be simple there is a table 1 in RDBMS. The RDBMS table is dynamic. Means everyday some new rows add to the RDBMS. I need to bring this table to SAS data mart.

For example the RDBMS table maintains customer transactions. For each transaction there is a unique id. Transaction id. amount1 adjusted amount, modified date.

transactionid   amount1 adjusted amount modified date

1                      10               0                  6/6/2012

2                       12               0                  5/6/2012

3                       4                 0                  4/6/2012

4                       2                0                    6/13/2012

This is the table status on 6/13/2012

on 06/14/2012 the status of the table is

transactionid   amount1 adjusted amount modified date

1                      10               0                  6/6/2012

2                       12               2                 6/14/2012

3                       4                 0                  4/6/2012

4                       2                0                    6/13/2012

5                      100               0                   6/14/2012

Now when you run this in DI studio you should get the RDMS table in SAS side. with 5 rows and row should be updated in SAS table.

we tried with update modify and delete, append code. code is working fine. Performance is low.

so wanted to try with DI studio.

thanks.

shivas
Pyrite | Level 9

Hi,

Comm table would be you RDBMS table and SASComm would be SAS Dataset.You need to change the load techique tab in table loader as below to update/Insert(if new record comes it will insert and old record changes it will update..try this).Hope this helps.

comm.png

Thanks,

Shiva

sassharp
Calcite | Level 5

@shivas

Thanks for your reply.

would you follow me here. That way I can send you direct message to you only. Just send me a request to follow me. I am already following you.

thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 921 views
  • 0 likes
  • 3 in conversation