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 -- . .
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.