topic Re: Creating Datasets from a parent dataset in SAS Programming

Creating Datasets from a parent dataset

librasonali — Thu, 02 Dec 2021 07:13:58 GMT

Hi,

I am having an oracle error as my source table is in oracle DB and the count of that table is more than 4,294,967,296. That is not supported in my current version but i need to submit my delivery on time.

so can you help me that if there are 4400000000 total records in dataset A ( table in oracle) and i want my first 4000000000 to be in dataset B from dataset A and rest 294,967,296 record in dataset C .

and in last i will append dataset B and dataset C to process further. Can you help me with the bold part? above thanks !!

Re: Creating Datasets from a parent dataset

yabwon — Thu, 02 Dec 2021 07:23:41 GMT

Do you want to have it in SAS or in Oracle?

In sas you can use firstobs= and obs= dataset options:

data A;
  do i = 1 to 10;
    output;
  end;
run;

data B;
 set A (obs=7);
run;
proc print;
run;

data C;
  set A (firstobs=8);
run;
proc print;
run;

But when you are pulling data from a data base I'm not sure if the data order is guaranteed.

Maybe if there is some sort of primary key which can allow to select all values less than "some value" to the first set and then all greater than equal from that "value" to the other set.

All the best

Bart

Re: Creating Datasets from a parent dataset

ChrisNZ — Thu, 02 Dec 2021 08:02:20 GMT

Do you really want to transfer 4 billion observations from SAS to Oracle? Why? How often do you need to refresh that data? What's the expected runtime?

If the limit you have is because you use a 32-bit platform, this kind of volume seems much too large.

The trouble is that Oracle may not always send the data in the same order, so if you can't use data values to determine the output table, you need to do this in one step:

data B C;
  set A;
  if _N_<=4e9 then output B; else output C;
run;

Re: Creating Datasets from a parent dataset

librasonali — Thu, 02 Dec 2021 09:13:35 GMT

hey I don't want from SAS to oracle , from oracle to SAS i want to read these many records

Re: Creating Datasets from a parent dataset

librasonali — Thu, 02 Dec 2021 09:14:18 GMT

I have tried obs option , but my table is in ORACLE so i need to use proc sql maybe.

Re: Creating Datasets from a parent dataset

Kurt_Bremser — Thu, 02 Dec 2021 09:31:09 GMT

So you need to create two separate tables in Oracle to accommodate the limits there?

Create a LIBNAME to the Oracle DB and schema, then run a variation of @ChrisNZ 's code:

data
  oracle.B
  oracle.C
;
set A;
if _N_ <= 4e9
then output oracle.B;
else output oracle.C;
run;

Re: Creating Datasets from a parent dataset

librasonali — Thu, 02 Dec 2021 12:52:38 GMT

my source table is in ORACLE and SAS is not able to read such many observation...
source table A
oracle.datasetA --- 4400000000 records
this i want spilt into 2 tables so that atleast I can read the data.

If you know any option in SAS that can help
I don't want to create two separate tables in Oracle to accommodate the limits there

Re: Creating Datasets from a parent dataset

Kurt_Bremser — Thu, 02 Dec 2021 14:34:40 GMT

If you can't even open the table from SAS to SET it in a data step, then you need to split it on the Oracle side.

This will need to be done in SQL.

I would define two views and use a variable along which you can easily split the data, like a transaction date or so.

Then, read the views separately.

Re: Creating Datasets from a parent dataset

Reeza — Thu, 02 Dec 2021 15:17:59 GMT

Is your Oracle table indexed? Honestly not seeing a way to process this all at once easily in a reasonable time frame.
I would partition my data, index it, and use PL/SQL to loop through all of them.

Re: Creating Datasets from a parent dataset

librasonali — Thu, 02 Dec 2021 16:49:29 GMT

Can you help me with the query?

Re: Creating Datasets from a parent dataset

Kurt_Bremser — Fri, 03 Dec 2021 20:32:45 GMT

Consult the Oracle documentation for CREATE VIEW (that's what I would also need to do). Furthermore, get in touch with your Oracle admins with regards to the necessary permissions and other technical details.

Re: Creating Datasets from a parent dataset

Patrick — Sat, 04 Dec 2021 00:22:43 GMT

@librasonali wrote:
my source table is in ORACLE and SAS is not able to read such many observation...

There is not such limit to my knowledge. What is your statement based on? Do you get a timeout or the like?

Oracle doesn't have a concept of row numbers and furthermore the only way to ensure Oracle returns rows always in the same order is to use an order by statement.

The normal approach is to reduce data volumes on the DB side prior to transferring it into SAS. The transfer from the DB to SAS is the likely bottleneck and could take a long time to run.

Do you really need the full table with all the detail on the SAS side or could you first reduce volumes on the DB side (like some selection for only specific values or pre-aggregation)? You can run SQL code on the DB side out of SAS and then only download the result set.

If you really need the full table then something like a bulkunload will likely perform much better. Downloading the full table will also require the necessary disk space to be available.

@Reeza is also hinting at other a bit more advanced options like Oracle partitions that - if the Oracle table is created this way - would allow to directly address the partitions and download in "chunks". ...but first look into processing on the Oracle side to reduce volumes prior to loading anything into SAS.