DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

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

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;

Accepted Solutions
Solution
‎02-19-2016 12:27 AM
Respected Advisor
Posts: 3,777

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

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;

Capture.PNG

View solution in original post


All Replies
Super User
Posts: 6,972

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎02-19-2016 12:27 AM
Respected Advisor
Posts: 3,777

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

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;

Capture.PNG

Frequent Contributor
Posts: 76

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

This macro is really handy - thanks!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 4268 views
  • 2 likes
  • 3 in conversation