Hello,
I am very new to SAS and I have a question, and I apologize if I am not clear or descriptive enough. I imported data from excel into SAS and I need to change the dates. Not every cell has a date. The blank cells are filled with a double hyphen --. The column attributes are character and the format and informat is $18. My question is how do I change the below date to month and year. Below is an example of how it looks like. Thank you for all of your help.
Date
--
--
--
--
--
--
AUG 05, 2015@15:30
AUG 05, 2015@15:30
--
--
--
--
--
--
AUG 03, 2015@17:20
AUG 03, 2015@17:20
AUG 03, 2015@17:20
AUG 03, 2015@17:20
AUG 03, 2015@17:20
AUG 03, 2015@17:20
--
--
--
--
Tested:
data have;
length date_Var $32;
input date_Var &;
datalines;
--
AUG 05, 2015@15:30
AUG 03, 2015@17:20
--
;
data want;
set have;
if length(date_Var) > 9 then do;
date_part1 = prxchange("s/(\w+)\s*(\d+)[, ]+(\d+)@.*/$2$1$3/o", 1, date_Var);
time_part1 = scan(date_var, 2, '@');
date_sas = input(date_part1, date.);
time_sas = input(time_part1, time.);
end;
format date_sas date9. time_sas time.;
run;
proc print; run;
Obs date_Var part1 part1 date_sas time_sas 1 -- . . 2 AUG 05, 2015@15:30 05AUG2015 15:30 05AUG2020 15:30:00 3 AUG 03, 2015@17:20 03AUG2015 17:20 03AUG2020 17:20:00 4 -- . .
Use SCAN() to separate the date and time portions
Use INPUT() to convert to SAS date/time
untested
if not missing(date) then do; *check for missing first so you don't get a bunch of errors in your log;
date_part1 = scan(date_var, 1, '@');
time_part1 = scan(date_var, 2, '@');
date_sas = input(date_part1, anydtdte.);
format date_sas date9.;
time_sas = input(time_part1, time.);
format time_sas time.;
end;
Tested:
data have;
length date_Var $32;
input date_Var &;
datalines;
--
AUG 05, 2015@15:30
AUG 03, 2015@17:20
--
;
data want;
set have;
if length(date_Var) > 9 then do;
date_part1 = prxchange("s/(\w+)\s*(\d+)[, ]+(\d+)@.*/$2$1$3/o", 1, date_Var);
time_part1 = scan(date_var, 2, '@');
date_sas = input(date_part1, date.);
time_sas = input(time_part1, time.);
end;
format date_sas date9. time_sas time.;
run;
proc print; run;
Obs date_Var part1 part1 date_sas time_sas 1 -- . . 2 AUG 05, 2015@15:30 05AUG2015 15:30 05AUG2020 15:30:00 3 AUG 03, 2015@17:20 03AUG2015 17:20 03AUG2020 17:20:00 4 -- . .
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.