01-31-2018 06:15 AM
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
01-31-2018 06:25 AM
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);
01-31-2018 09:49 AM - edited 01-31-2018 09:51 AM
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);
01-31-2018 06:44 AM
I assume this is the length set at the database end? You can use compress dataset options:
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.