BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tushar
Obsidian | Level 7

Hi ALL,

I am getting error while clustering two SPDS table. Also checked criteria that are needed two satisfie when you cluster two SPDS table. mentioned in below link.

http://support.sas.com/documentation/cdl/en/spdsug/64018/HTML/default/viewer.htm#n0jg57dvm7iajyn1olk...

this is something i have checked using Proc contents with two clustering table. Any suggestions on this?

the error i am getting is below.

ERROR: Member table not compatible with other cluster members. Compare CONTENTS

for some reason i can't share the code over here but looking for suggestion from anyone who have worked or faced same kind of issue.

Regards,

Tushar Jagtap.

1 ACCEPTED SOLUTION

Accepted Solutions
Tushar
Obsidian | Level 7

Hi,

i got my problem solve, I was using Proc contents without verbose=Yes option. later i done with proc contents with verbose=yes and found difference between minmaxvarlist in tables. That solved my issue after making changes.

Thanks both of you again for your helpful information.

Regards,

Tushar J

View solution in original post

9 REPLIES 9
Tushar
Obsidian | Level 7

Is this necessary to have table compress when we are using engine as SASSPDS?

umashankersaini
Quartz | Level 8

Hi tushar,

yes, it is necessary to use compression when writting to SPD server table.

Regards

Uma Shanker Saini

umashankersaini
Quartz | Level 8

Hi Tushar,

I know, you are already done with your homework but may be this one could help.

1.When loading a SPD server table always sort the table by the variable used to join that SPD server table to other SPD server tables

2.When ordering the "Where, aggregations and counts" variables in the sort routine go from lowest to highest.

3.Always use compression on SPD server domain.

4.You can use the LIBNAME options  COMPRESS=Yes or the SPD server macro SPDSDCMP=yes to ensure all tables written to an SPD server domain are compressed.

5.Always replace DATA STEP modify code with PROC APPEND using the SPD Server option UNIQUESAVE=REP

6. To use PROC APPEND with UNIQUESAVE=REP the SPD Server table must have a unique index.

Regards

Uma Shanker Saini

umashankersaini
Quartz | Level 8

Hi Tushar,

When you are quering SPDS tables, always try to use these LIBNAME options:

1. IP=yes

If the ANSI Standard SQL submitted to SPD server would run faster in Pass-through sql, the query will be converted to pass through SQL.

2. LIBGEN=yes

Required when SAS Marketing Automation uses SPD Server tables.

3. DISCONNECT=yes

Kills the SPD server proxy server when the libname is cleared.

4. COMPRESS=yes

Ensure compression is used when writing to SPD Server table

Regards

Uma shanker Saini

umashankersaini
Quartz | Level 8

Hi Tushar,

Try to check this one as well...

You can only perform four functions on a dynamic cluster table: creating a cluster, undoing a

cluster, adding tables, and listing member tables. To perform any function other than these four,

you must first undo the cluster with the CLUSTER UNDO command. Then, any changes must be

made to all individual tables in the cluster. Once changes are made, you can re-create the cluster

using the CLUSTER CREATE command.

All member SAS SPD Server tables in a cluster must be identical; that is, they must have the

same definitions for columns names, column widths, table indexes, formats on the columns,

partition size, compression ON/OFF, and so forth. You cannot change table names, column

names, column formats, indexes, or anything else about any member table, including the cluster

  1. name.

The owner of all member tables must be the same. To create a cluster, the owner of the cluster

must be the same user that owns the member table. For example, suppose USER1 creates a

table, and USER2 also creates a table. These tables cannot be members of the same cluster,

regardless of what ACL permissions are granted on the tables


Regards

Uma Shanker Saini

LinusH
Tourmaline | Level 20

Perhaps you could use PROC COMPARE to see if you have any differences. It will cover all attributes that are common to Base and SPDS, but perhaps not SPDS specific ones.

And no, you don't have to use compression with SPDS. If this was the case, I'm pretty sure it would be mandatory or default. Use compression when appropriate, pretty much in the same cases as with Base SAS tables. But of course, in a cluster, all tables need to be either compressed, or uncompressed.

Data never sleeps
Tushar
Obsidian | Level 7

Thanks LinusH  and


Tushar
Obsidian | Level 7

Hi,

i got my problem solve, I was using Proc contents without verbose=Yes option. later i done with proc contents with verbose=yes and found difference between minmaxvarlist in tables. That solved my issue after making changes.

Thanks both of you again for your helpful information.

Regards,

Tushar J

umashankersaini
Quartz | Level 8

Hi Tushar,

Thank you so much for updating us. It would be add-on note into my bucket.

thank you again.

Regards

Uma Shanker Saini

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3297 views
  • 8 likes
  • 3 in conversation