convert character YYYY-mm-dd HH:MM:SS.000000 to date (and overwrite old variables)

[mduarte]

Hi -  I have imported data into SAS using proc sql to execute a stored procedure.

Unfortunately the date and datetime variables have been imported as character and I would like to convert them appropriately. 

Based on this post, I have been able to convert to a new variable (data_with_new_vars below works as expected), however I would like to know if it is possible to overwrite the original variables with the correct format (data_with_old_vars does not work as expected).  This is because I want to use the same variable names.

data data_with_char;
 my_date = '2014-09-01 21:19:45.0000000';
 my_datetime = '2014-09-01 21:19:45.0000000';
run;

data data_with_new_vars;
set data_with_char;
 my_date_new = input(my_date, YYMMDD10.);
 format my_date_new date10.;
 my_datetime_new = input(my_datetime, ANYDTDTM19.);
 format my_datetime_new DATETIME21.;
run;

data data_with_old_vars;
set data_with_char;
 my_date = input(my_date, YYMMDD10.);
 format my_date date10.;
 my_datetime = input(my_datetime, ANYDTDTM19.);
 format my_datetime DATETIME21.;
run;

[Kurt_Bremser]

In order to replace a character variable with a numeric, do this:

data want (drop=oldvar);
set have (rename=(var=oldvar));
format var someformat.;
var = somefunctionof(oldvar);
run;

You cannot change the type of a variable in place. You need to create a new variable and use the rename option so that it gets the name of the orginal variable.

[data_null__ · Accepted solution]

This is a good opportunity to learn a little macro as this task is well suited.  You also get to learn how to generate a unique temporary variable name (almost certainly) using and automatic variable SYSINDEX.  There are many variations on how to set the default you could for example look at the var name for the a string like DATE/DATETIME and use that to set the defaults.  The rest is the same code you already have plus the RENAME and DROP ( the answer to your original question).  Getting the LABEL from the old variable to the new variable takes a bit more work.

data data_with_char;
   my_date = '2014-09-01 21:19:45.0000000';
   my_datetime = '2014-09-01 21:19:45.0000000';
   run;


%macro c2date(var,informat,format);
   %if %superq(informat) eq %then %let informat=yymmdd10.;
   %if %superq(format)  eq %then %let format=yymmdd10.;
   format __&sysindex &format;
   informat __&sysindex &informat;
   __&sysindex = input(&var,&informat);
   rename __&sysindex=&var;
   drop &var;
   %mend;
options mprint=1;
data convert;
   set data_with_char;
   %c2date(my_date);
   %c2date(my_datetime,ANYDTDTM19.,Datetime.);
   run;
proc contents varnum;
proc print;
   run;

49         data convert;
50            set data_with_char;
51            %c2date(my_date);
MPRINT(C2DATE):   format __12 yymmdd10.;
MPRINT(C2DATE):   informat __12 yymmdd10.;
MPRINT(C2DATE):   __12 = input(my_date,yymmdd10.);
MPRINT(C2DATE):   rename __12=my_date;
MPRINT(C2DATE):   drop my_date;
52            %c2date(my_datetime,ANYDTDTM19.,Datetime.);
MPRINT(C2DATE):   format __13 Datetime.;
MPRINT(C2DATE):   informat __13 ANYDTDTM19.;
MPRINT(C2DATE):   __13 = input(my_datetime,ANYDTDTM19.);
MPRINT(C2DATE):   rename __13=my_datetime;
MPRINT(C2DATE):   drop my_datetime;
53            run;

[mduarte]

This macro is really handy - thanks!