Hi,
Had an oddity whilst reading in some iso date data. The data looks something like:
AESTDTC
2013-02-02T08:00
2013-02-01
Now first program used this syntax (in a proc sql):
case when length(strip(AESTDTC))=16 then input(put(strip(AESTDTC)||":00",$18.),is8601dt.)
when length(strip(AESTDTC))=10 then input(put(strip(AESTDTC)||"T:00:00:00",$18.),is8601dt.)
else . end) as TMP_VAR format=datetime20.
This gave a peculier result of:
AESTDTC
TMP_VAR
2013-02-02T08:00 02FEB2013 08:00:02
2013-02-01 01FEB2013 00:00:02
Notice the seconds on the Iso date. Anyone seen this or know why, just out of interest? I have now corrected this to use input:
case when length(strip(AESTDTC))=16 then input(AESTDTC,is8601dt.)
when length(strip(AESTDTC))=10 then input(AESTDTC,is8601da.)
else . end) as TMP_VAR format=datetime20.
Thanks
Rob
hmm,
Would it be the $18. ouput being one too short 16+3 chars is 19 chars.
Indeed leaving out code not necessary is also causing less trouble... (See simplified code of jagadish)
You have a datetime value defined.... It is calculated as the seconds relative to 1jan1960 (LR concepts).
So the value of 2Feb2013:00:00:00 will become: ( 53 years, 33 days ) *24 *3600 = 1 675 404 000 === . (53*356,25 + 33) 24 hour/ day 3600 seconds/hour (approximity)
Probably hiiting a numeric precison flaw of an internal conversion steps.: 31560 - Choosing your degree of numeric precision
Hi,
Please check the below code, its is producing the accurate results for time as well
proc sql;
create table text_ as
select date, case
when length(strip(date))=16 then input(date||':00',is8601dt.)
when length(strip(date))=10 then input(compress(date||'T00:00:00'),is8601dt.)
else .
end as tmp_var format=datetime20. from text;
quit;
output;
Obs | date | tmp_var |
1 | 2013-02-02T08:00 | 02FEB2013:08:00:00 | |
2 | 2013-02-01 | 01FEB2013:00:00:00 |
Thanks,
Jagadish
hmm,
Would it be the $18. ouput being one too short 16+3 chars is 19 chars.
Indeed leaving out code not necessary is also causing less trouble... (See simplified code of jagadish)
Hi,
Thank you for your replies. I can safely say I feel daft now, not being able to count how many characters are in my string. You are indeed correct it should be $19. and hence was rounding a shortned value. Time for cup of tea.
Rob
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.