Proc sql column lenght always in 4000 bytes automatically ..

[srinidelite]

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 

[Satish_Parida]

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);

[srinidelite]

It doesn't work for me 

[srinidelite]

deleted

[Satish_Parida]

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);

[RW9]

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.