topic Re: sas7bdat work with partitions in SAS Programming

sas7bdat work with partitions

claudiodejesusa — Wed, 17 Aug 2022 03:57:33 GMT

Hi everybody,

I need some help about options for large datasets and their support and scalable in the time.

Does the concept of partitions in DBMS works on sas tables? My theory is.. i can have physical tables about year-month and all together represent the complete table as logical representation.

Do you have any idea how to work partitions on sas7bdat? or what other options to deal big tables?

Thanks,
Claudio

Re: sas7bdat work with partitions

Kurt_Bremser — Wed, 17 Aug 2022 06:08:04 GMT

Suppose you import data on a monthly basis, and store them as

data_202201
data_202202
data_202203

and so on.

You can then create views like this:

data data_2022 / view=data_2022;
set
  data_202201
  data_202202
  data_202203
  /* ... */
;
run;

The names of the individual datasets can be created and stored in a macro variable in a SQL (SELECT INTO) or DATA (CALL SYMPUTX) step dynamically, as needed.

Re: sas7bdat work with partitions

LinusH — Wed, 17 Aug 2022 07:37:13 GMT

There are no support for partitions for SAS Base engine files.

From a performance perspective, I suggest that you try out SPDE engine, which phisically divide data sets into smaller filer, so multi-threaded reads could happen.

With an additional license (SPD Server) you can use logical partitions (so called clusters) which i similar to DBMS partitions.

That said, there is an ongoing transition towards Viya, where SPDE will still be accessible (only RO for backward compability).

So far, no SPD Server client have been released AFAIK for Viya.

Re: sas7bdat work with partitions

Ksharp — Wed, 17 Aug 2022 12:21:17 GMT

As LinusH pointed out . Two options:
1) if you have money ,buy SAS SPD Server .
2) Try SPDE engine ,but it has many limitation compare with Base V9 engine .

Re: sas7bdat work with partitions

LinusH — Fri, 23 Sep 2022 06:57:03 GMT

Update - there is a SPD Server client available for Viya. This means that you pretty much can use SPD Server from Viya SAS Compute Server like you do in SAS 9.4.
https://documentation.sas.com/doc/en/pgmsascdc/v_006/spdsug/n0vg1g427lrxzgn1fhbdqw9hm319.htm

I guess this is available so that costumers can migrate to Viya, without having to replace their data store. That said, SPD Server )server part) will most likely not be migrated to Viya. My guess is that SAS will create bundles with other 3rd arty DBMS, like Single Store.