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
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);
It doesn't work for me
deleted
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);
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: