Convert All Variables in Dataset to Character without Loosing any original formatting

[Tom]

---

@ijm_wf wrote:

Please note that after the conversions, the final SAS dataset containing all character variables with $1000. format will be appended to a Teradata table with corresponding varchar(1000) fields.

---

There is no need to make the variables all have a length of 1000 just because you are going to insert the values into a field that has that length.  Now if some of the variables have a length that is greater than 1000 then you might have an issue with trying to insert that value into your Teradata table.  In that case you might need to change the LENGTH of the variable (and lose some of the data).  Just changing the format attached wouldn't truncate the values.  Note that there is no need to attach a $1000. format to any character variable, SAS already knows how to display character variables so no special formatting instructions are needed.

However depending on how you do the conversion it might be easier to just make them all have the same length than take the effort to make the length match the length of the target variable in Teradata table.  Note that the double transpose method proposed on this thread will make the length of all of the variable long enough for the longest value of any of the variables.

[Tom]

If you get a list of the names of the variables you can use that to generate code.  PROC TRANSPOSE is an easy way to get the list of variable names.

%let dsn=sashelp.class;
proc transpose data=&dsn(obs=0) out=names;
  var _all_; 
run;

Then use that list to generate code to convert the values to character using the VVALUE() function. Just makeup a new name for each new character variable and then rename it back to the original name.  You might want to make a VIEW that does this instead of making a permanent copy of the original dataset.

data _null_;
  set names end=eof;
  varnum+1;
  length newname $32 oldname $64;
  newname=cats('__v',varnum);
  oldname=nliteral(_name_);
  if _n_=1 then call execute("data converted/view=converted; set &dsn;");
  call execute(catx(' '
   ,'length',newname,'$1000;'
   ,newname,'=vvalue(',oldname,');'
   ,'keep',newname,';'
   ,'rename',newname,'=',oldname,';'
  )); 
  if eof then call execute('run;');
run;

Then just push the data from the view into your TD database.

proc append base=TDLIB.TD_table_name data=converted force;
run;