topic Re: Convert character variable to datetime variable (current format is YYYY-MM-DDTHH:MM:SS.000-05) in SAS Studio

Convert character variable to datetime variable (current format is YYYY-MM-DDTHH:MM:SS.000-05)

andreaat — Fri, 09 Aug 2019 15:20:50 GMT

I am a beginner SAS University edition user so I'm not too familliar with all the terminology.

I'm trying to convert a character variable into a datetime variable. My variable is called startdate_q and the format is currently YYYY-MM_DDTHH:MM: SS.000-05. One cell under the variable startdate_q reads 2013-03-06T12:23:20.000-05

Im not sure what the last 7 characters represent (.000-05) so I would like to get rid of them.

Can someone please give me the exact code to convert this character variable into a datetime variable?

I tried using this code but it didnt work

data new;
set old;
startdate_Q_new = startdate_Q;
format startdate_Q_new yymmdd10.;
run;

Thanks!

Re: Convert character variable to datetime variable (current format is YYYY-MM-DDTHH:MM:SS.000-05)

novinosrin — Fri, 09 Aug 2019 15:31:57 GMT

data want;
char_datetime='2013-03-06T12:23:20.000-05';
want_date_time=input(char_datetime, B8601DT.);
format want_date_time datetime20.;
run;

Re: Convert character variable to datetime variable (current format is YYYY-MM-DDTHH:MM:SS.000-05)

andreaat — Fri, 09 Aug 2019 15:36:19 GMT

I ran that code and it didn't work. 3 new numeric variables were created: want_date_time, k and k1 and none of them contain any data in them. No error code came back but this is what the log said

 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       76:25   
 NOTE: Variable k is uninitialized.
 NOTE: Variable k1 is uninitialized.
 NOTE: There were 30128 observations read from the data set WORK.CLSA2.
 NOTE: The data set WORK.CLSA3 has 30128 observations and 2363 variables.
 NOTE: DATA statement used (Total process time):
       real time           10.68 seconds
       cpu time            4.94 seconds

Re: Convert character variable to datetime variable (current format is YYYY-MM-DDTHH:MM:SS.000-05)

novinosrin — Fri, 09 Aug 2019 15:38:32 GMT

Hello @andreaat I owe you an apology for the EDIT that I did and didn't notify you. Please accept my sincere apologies. The edited version latest is

data want;
char_datetime='2013-03-06T12:23:20.000-05';
want_date_time=input(char_datetime, B8601DT.);
format want_date_time datetime20.;
run;

I am really sorry again!

Re: Convert character variable to datetime variable (current format is YYYY-MM-DDTHH:MM:SS.000-05)

andreaat — Fri, 09 Aug 2019 15:50:54 GMT

No worries Thank you for helping me with this!

When I use that code, only one row of data is made with the old variable chart_datetime and the new variable want_datetime.

I added in a set statement to your code and now all of the new want_datetime data points have the date and time of 06MAR2013:12:23:20.

This is how my code looked

data want;
set have;
char_datetime='2013-03-06T12:23:20.000-05';
want_date_time=input(char_datetime, B8601DT.);
format want_date_time datetime20.;
run;

Each row of data has a different time and the new variable (want_date_time) produces the same date for all datapoints. Any suggestions on what I should do to make sure that each row of data is produced with its corresponding date and time?

Thank you!

Re: Convert character variable to datetime variable (current format is YYYY-MM-DDTHH:MM:SS.000-05)

Tom — Fri, 09 Aug 2019 16:05:54 GMT

@andreaat wrote:

No worries Thank you for helping me with this!

When I use that code, only one row of data is made with the old variable chart_datetime and the new variable want_datetime.

I added in a set statement to your code and now all of the new want_datetime data points have the date and time of 06MAR2013:12:23:20.

This is how my code looked
data want;
set have;
char_datetime='2013-03-06T12:23:20.000-05';
want_date_time=input(char_datetime, B8601DT.);
format want_date_time datetime20.;
run;
Each row of data has a different time and the new variable (want_date_time) produces the same date for all datapoints. Any suggestions on what I should do to make sure that each row of data is produced with its corresponding date and time?

Thank you!

Did you read the code you ran? You are saying, in order. 1) Create a new dataset named WANT. 2) read in the data from an existing dataset named HAVE. 3) Set CHAR_DATETIME to a constant text value, so it will have the same value for every observation read from HAVE. It the variable existed in HAVE the original value from HAVE will be overwritten, and if it didn't exist then a new variable will be created. 4) Convert this constant in CHAR_DATETIME into numeric datetime value in WANT_DATE_TIME by using the INPUT() function. 5) attach the DATETIME format to WANT_DATE_TIME.

You probably want to change the dataset names in the DATA and SET statement. Remove the first assignment statement. And change the variables names in the remaining assignment statement and the format statement to match the names of the variable you actually have the variable you want to create.

Re: Convert character variable to datetime variable (current format is YYYY-MM-DDTHH:MM:SS.000-05)

novinosrin — Fri, 09 Aug 2019 16:33:22 GMT

Sir Tom has explained at length that is exquisite akin to that of a text book tutorial. I hope you understood those points.

So if you did understand, you would have hopefully corrected your code to

data want;
set have;
want_date_time=input(char_datetime, B8601DT.);
format want_date_time datetime20.;
run;