topic Re: How do I reduce the lengths of columns in proc sql passthrough to pull less data across the netw in SAS Programming

How do I reduce the lengths of columns in proc sql passthrough to pull less data across the network

TimCampbell — Wed, 16 Sep 2020 15:35:45 GMT

Hi,

I have a table in an oracle database where one of the text columns is defined to have a length of 400 but the data only has a max length of 20 characters.

The following query works for me pulling the data out of oracle and storing it in a work table with the right column length.

proc sql;
    connect to oracle (authdomain=XXXXX path=XXXX);
    create table work.table as
        select  
			column_with_length400 length=20
        from
            connection to oracle
            (
                select      
                    column_with_length400
                from
                    oracleschema.tablename 
            )   
        ;
    disconnect from oracle;
quit;

The problem I have is that as well as limiting the size of the final dataset, I want to limit the amount of data needing to be pulled across the network and the speed improvement on the query when specifying 'length=20' doesn't seem to be very much compared to when I don't.

My suspicion is that SAS is pulling the full size of the column across the network and then limiting it to 20 characters in the output.
Does anyone know how I can get SAS to limit the data to 20 characters at the database end so less data is pulled across the network?

Obviously the example query above isn't the full query I am running and there are other details to the query that mean I am tied to using the 'connect to oracle' method here.

Thanks,

Tim

Re: How do I reduce the lengths of columns in proc sql passthrough to pull less data across the netw

CurtisMackWSIPP — Wed, 16 Sep 2020 15:41:12 GMT

I don't think there is a way to tell SAS to do it. However, you could modify the passthrough query to trim the value. The exact syntax would depend on the oracle, but something like this.

proc sql;
    connect to oracle (authdomain=XXXXX path=XXXX);
    create table work.table as
        select  
			column_with_length400 length=20
        from
            connection to oracle
            (
                select      
                    substr(column_with_length400,1,20) as column_with_length400
                from
                    oracleschema.tablename 
            )   
        ;
    disconnect from oracle;
quit;

Re: How do I reduce the lengths of columns in proc sql passthrough to pull less data across the netw

TimCampbell — Thu, 17 Sep 2020 09:45:04 GMT

Thanks,

the substr syntax is correct but the column still comes back as length 400.

also tried the trim function and got the same results.

Re: How do I reduce the lengths of columns in proc sql passthrough to pull less data across the netw

CurtisMackWSIPP — Thu, 17 Sep 2020 14:08:42 GMT

Good to know. Sorry I don't have any other ideas.

Re: How do I reduce the lengths of columns in proc sql passthrough to pull less data across the netw

TimCampbell — Thu, 17 Sep 2020 14:27:00 GMT

Short version - substr does work.

longer version - the issue in the columns coming out larger than expected is from an encoding change.

I think I found my answer here...

https://communities.sas.com/t5/SAS-Data-Management/Oracle-column-length-increase-when-metadata-is-registered-to-DI/td-p/31897

It looks like I've missed a trick in my testing and hadn't picked up the multiple of 4 thing that this person hit.

We recently moved server to an environment that also has a viya installation so our encoding changed to utf-8 and i'm guessing that sas is now picking up that the oracle tables have 4 bytes per character where it used to ignore it on our old server.

further checks showed that if i substr the column down to 1 character I do get 4 bytes back so I think it might be doing the right thing.

Thanks for your help