topic Re: Convert All Variables in Dataset to Character without Loosing any original formatting in SAS Programming

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

ijm_wf — Fri, 11 Oct 2019 14:24:16 GMT

I have a dataset with ten variables, VAR1-VAR10. Four of the variables are numeric dates and have a MMDDYYD10. format and the other six are character and have a $1000. format. My objective is to convert all ten of the variables to character with a $1000. format. It is necessary, however, that the dates maintain their originally formatting after being converted to character. For example if a date displays as 10-11-2019 before the conversion, it must display as 10-11-2019 after the conversion.

What is the best way to convert all variables at once to character $1000.? Please provide code examples.

Thanks.

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

PaigeMiller — Fri, 11 Oct 2019 14:38:17 GMT

@ijm_wf wrote:

I have a dataset with ten variables, VAR1-VAR10. Four of the variables are numeric dates and have a MMDDYYD10. format and the other six are character and have a $1000. format. My objective is to convert all ten of the variables to character with a $1000. format. It is necessary, however, that the dates maintain their originally formatting after being converted to character. For example if a date displays as 10-11-2019 before the conversion, it must display as 10-11-2019 after the conversion.

What is the best way to convert all variables at once to character $1000.? Please provide code examples.

First, you can't convert numeric variables to character, this is not possible. You can create new character variables of the desired length, that look identical to the formatted numeric variable, but why bother? It seems like unnecessary work. Why do you need numeric variables as character with format $1000.?

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

Kurt_Bremser — Fri, 11 Oct 2019 14:52:47 GMT

The best way is to not do that. Character variables with a defined length of 1000 waste 996 (!) bytes when dates are stored there (numeric variables containing dates can be defined with a length of just 4), and you lose all the fine methods (functions, calculations, formats) of dealing with SAS date values.

Don't waste your time on something stupid.

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

Reeza — Fri, 11 Oct 2019 14:53:12 GMT

1. Query SASHELP.VCOLUMN or PROC CONTENTS to get the list of variables and types, and number of variables

2. Use VVALUEX to convert to formatted character value

3. Drop old variables.

Rough sketch of what that may look like.

data want;

set have;

array _haveN(*) _numeric_;
array _haveC(*) _character_;

array newVar(*) $100. newVar1-newVar10;

do i= 1 to dim(_haveN);
newVar(i) = vvaluex(vname(_haveN(i)));
end;

do j=i to dim(_haveC);
newVar(j) = vvaluex(vname(_haveC(i)));
end;

run;

@ijm_wf wrote:

I have a dataset with ten variables, VAR1-VAR10. Four of the variables are numeric dates and have a MMDDYYD10. format and the other six are character and have a $1000. format. My objective is to convert all ten of the variables to character with a $1000. format. It is necessary, however, that the dates maintain their originally formatting after being converted to character. For example if a date displays as 10-11-2019 before the conversion, it must display as 10-11-2019 after the conversion.

What is the best way to convert all variables at once to character $1000.? Please provide code examples.

Thanks.

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

ijm_wf — Fri, 11 Oct 2019 15:06:00 GMT

Paige, what is the recommended way to "create new character variables of the desired length, that look identical to the formatted numeric variable"?

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

PaigeMiller — Fri, 11 Oct 2019 15:08:10 GMT

I believe @Kurt_Bremser answered this. The recommended way to do this conversion so you have character variables that appear to be identical to the formatted numeric variables is NOT doing it at all.

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

Reeza — Fri, 11 Oct 2019 15:16:02 GMT

I don't recommend this either by the way and in 15 years don't think I've ever needed this. I've done similar things partially, creating data sets that have the raw value and the formatted value but that's usually when I need to transfer it to someone else who may not have SAS or to explore in Excel.

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

data_null__ — Fri, 11 Oct 2019 15:36:05 GMT

It is easy with PROC TRANSPOSE and there are valid reasons for doing. Notice variable names and labels remain the same.

data cars;
   _obs_ + 1;
   set sashelp.cars;
   run;
proc transpose data=cars out=flip;
   by _obs_;
   var _all_;
   run;
data flip;
   set flip;
   where upcase(_name_) ne '_OBS_';
   col1 = left(col1);
   run;
proc transpose data=flip out=flop(drop=_name_);
   by _obs_;
   var col1;
   run;
proc contents varnum;
proc print;
   run;

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

Kurt_Bremser — Fri, 11 Oct 2019 15:35:44 GMT

@data_null__ wrote:

... there are valid reasons for doing.

Since no reasons were given, I see it as my duty here to protect a newbie from doing something that is, ahem, not very efficient.

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

ijm_wf — Fri, 11 Oct 2019 15:45:54 GMT

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.

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

Kurt_Bremser — Fri, 11 Oct 2019 15:48:17 GMT

@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.

Wouldn't that also need a transpose, as such structures are usually used in name/value tables?

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

Tom — Fri, 11 Oct 2019 16:56:22 GMT

@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.

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

Ksharp — Sat, 12 Oct 2019 11:53:52 GMT

@Reeza Why not use VVALUE() which convert variable into character variable with its format .

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

Tom — Sat, 12 Oct 2019 15:49:11 GMT

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;

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

Reeza — Sat, 12 Oct 2019 19:07:54 GMT

@Ksharp VVALUE requires a literal name, VVALUEX takes an expression/formula, such as the result from VNAME().

@Ksharp wrote:

@Reeza Why not use VVALUE() which convert variable into character variable with its format .

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

data_null__ — Mon, 14 Oct 2019 17:25:20 GMT

@Reeza wrote:

@Ksharp VVALUE requires a literal name, VVALUEX takes an expression/formula, such as the result from VNAME().

@Ksharp wrote:

@Reeza Why not use VVALUE() which convert variable into character variable with its format .

I think @Ksharp is saying you could have done it more directly using an array reference in VVALUE.

   vvalue(_haveC[I]);

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

Reeza — Tue, 15 Oct 2019 00:07:40 GMT

Ah...thanks for the correction!