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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.