SAS date format returning censored

Question

I have a column called Last Payment as such

last payment
12DEC09:00:00:00

all the observations follow this structure, I've tried taking a substring such that

data want;
set have;
last_payment=substr(last_payment,1,7);
run;

that doesn't work, I've tried formatting the date with the date7. and date.9 but both just return ********, can someone help me format it into a ddmmmyy ty.

score 2 · Answer 1 · answered Mar 14 '18 at 12:05

2

You have to use datapart function before formating it in date7. or date9. Example:

data new;
format date_new date9. ;
date_new = datepart("12DEC09:00:00:00"dt);
run;

answered Mar 14 '18 at 12:05

ofzy

95
11

if all the date / time was different throughout the dataset could I use a * in the ""? – 78282219 Mar 14 '18 at 12:11
1

you need to replace the "12DEC09:00:00:00"dt with the column name. – ofzy Mar 14 '18 at 12:17

momo1644 · Answer 2 · 2018-03-14T12:32:57.500

The censored text means wrong format used.

if last_payment is a string then replace "datetime_as_string " with last_payment.
if last_payment is datetime then replace "datetime_as_dt" with last_payment.

The code below will handle the two cases where you are reading the date time as string and numeric values.

Code:

data new;
format  datetime_as_dt datetime21. dt_to_date date7. datetime1 date7.;
datetime_as_dt="12DEC09:00:00:00"dt;
datetime_as_string ="12DEC09:00:00:00";
/*Converting datetime to date*/
dt_to_date= datepart(datetime_as_dt);
/*converting datetime string to date*/
/*Option1: Convert string to datetime then extract date*/
datetime1 = datepart(input(datetime_as_string,datetime21.));
/*Option2: Extract date from string then convert to date*/
datetime2 = scan(datetime_as_string,1,':');
put _all_;
run;

Log:

datetime_as_dt=12DEC2009:00:00:00 
datetime_as_string=12DEC09:00:00:00
dt_to_date=12DEC09 
datetime1=12DEC09  
datetime2=12DEC09

Table created:

SAS date format returning censored

2 Answers2