SAS issue with dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

SAS issue with dates

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

--

--

--

--

 

 


Accepted Solutions
Solution
‎03-01-2018 06:54 PM
Esteemed Advisor
Posts: 5,526

Re: SAS issue with dates

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     --                                                  .           .
PG

View solution in original post


All Replies
Super User
Posts: 23,705

Re: SAS issue with dates

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;

Occasional Contributor
Posts: 11

Re: SAS issue with dates

This may be as stupid question, but how do I check for missing?
Solution
‎03-01-2018 06:54 PM
Esteemed Advisor
Posts: 5,526

Re: SAS issue with dates

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     --                                                  .           .
PG
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 113 views
  • 0 likes
  • 3 in conversation