Dealing with length when manipulating tables

[onimisi_esho]

I created a table on SAS work.sas_table with 5 columns(3 char and 2 numeric) by running a select query from an external database(hive presto). I got a warning that "the following columns could have a lenght in SAS of 32767. if so, SAS performance is impacted."

I tried to work with work.sas_table then got this error: "ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000000019'n because the length of the index value (32767 bytes) is too
       large. The index page size (currently 32767) must be large enough to store three index values plus a small overhead
       structure. Reduce the length of the index value or use the IBUFSIZE= option to increase the index page size (up to a maximum
       of 32,767 bytes).
ERROR: :Unable to create temporary index while processing summary functions."

On checking the properties of wor.sas_table I discovered the the reason for the first error looking at the length

Name	Type	Length	Format	Informat	Label
Col 1	Character	32767	$32,767.00	$32,767.00	month
Col 2	Numeric	8	20	20	msisdn_key
Col 3	Numeric	8	11	11	smartphones
Col 4	Character	32767	$32,767.00	$32,767.00	imei
tech	Character	32767	$32,767.00	$32,767.00	tech

Please how do I alter the table and possible reduce the length of the character columns of work.sas_table. Is there a function in SAS I can use? I am running SAS Enterprise

[jklaverstijn · Accepted solution]

Hi,

A good approach here is to first create the target table without any data but with the definitions as you want them. Then you run your extract and do a force append of that to the dataset you created. This will make certain you get what you want instead of letting the access engine decide. For example:

/**
 * Step 1: create the target table.
 */
data target;
    attrib col1 length=$10;
    attrib col2 length=$40;
    attrib col3 length=8;
    call missing(col1, col2, col3);
    stop;
run;

/**
 * Step 2: define the source, preferrably as a view.
 */
proc sql;
    create view source as (select col1, col2, col3 from ....);
quit;

/**
 * Step 3: Load the source data into the target table
 */
proc append base=target data=source force;
run;

This is a pattern that is typically implemented in the data loader transform in SAS DI Studio.

Hope this helps,

-- Jan.

[onimisi_esho]

hi @jklaverstijn 

Thank you so much.

It worked like a charm.

I am grateful.

Best regards,