topic Re: Proc sql column lenght always in 4000 bytes automatically .. in SAS Programming

Proc sql column lenght always in 4000 bytes automatically ..

srinidelite — Wed, 31 Jan 2018 11:15:26 GMT

I Have few Test Cases all of them run using Proc sql and one key thing i have noticed that the variable / column value always set to 4000 bytes automatically but is there a way to dymically assign based on the values ? for each dataset .

Because dataset1 value might enough with 30 bytes but datset2 value might not be enough with 30 bytes .. so i need to tell the sas to allocate the memory (lenght ) based the data it process !

Is there anyway i can do it ? experts pls help me out

Re: Proc sql column lenght always in 4000 bytes automatically ..

Satish_Parida — Wed, 31 Jan 2018 11:25:29 GMT

You can use a Macro to pass the lengths to the columns during table creation

%macro creation(var1=, var2=);

proc sql;

create table testcase (

var1 char(&var1.),

var2 char(&var2.)

);

quit;

%mend;

%creation(var1=12, var2=8);

Re: Proc sql column lenght always in 4000 bytes automatically ..

RW9 — Wed, 31 Jan 2018 11:44:11 GMT

I assume this is the length set at the database end? You can use compress dataset options:

https://support.sas.com/rnd/base/datastep/compression.html

This is the simplest and comrpess the dataset, so it does not matter how long the lengths are.

However if you want to get more complicated - i.e. more programming, harder to maintain, less robust, then you need to code it in your self. First ascertain the max length of each variable, then use that information to reset the lengths, or generate the SQL code to set the lengths. You don't really save anything doing it this way either.

Re: Proc sql column lenght always in 4000 bytes automatically ..

srinidelite — Wed, 31 Jan 2018 11:45:59 GMT

It doesn't work for me

Re: Proc sql column lenght always in 4000 bytes automatically ..

srinidelite — Mon, 18 Jun 2018 05:42:27 GMT

deleted

Re: Proc sql column lenght always in 4000 bytes automatically ..

Satish_Parida — Wed, 31 Jan 2018 14:51:19 GMT

I have made a small change, if you see any error, please post the log

%macro creation(Value);

     Proc Sql;
        Create table Target as 
            select 
                t2.NUM,
                t2.ID,
                t2.NCEID,
                t4.POLOBJECTID,
                t3.PRODUCTVERSIONOBJECTID,
                t4.EXTERNALIDENTIFIER as ExternalID,
                t4.Value as Value  length = &Value.                     *Change;
            from  &wrkschema.. lkp_policyinstance t2
                inner join &tgtschema.. INS t3
                    on t2.rpinstid  = t3.POLICYINSTANCEID     and t2.cdnumpol  =  '002011216756'
                inner join &tgtschema.. POLOBJt4
                    on t3.identifier = t4.POLINOBJ
                Where t4.externalidentifier =  &extrniden. and t3.CHECK= &objMotorbike.
        ;
    quit;

%mend;

%creation(Value=40);