BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

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) 

---->-- ja karman --<-----

View solution in original post

4 REPLIES 4
jakarman
Barite | Level 11

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

---->-- ja karman --<-----
Jagadishkatam
Amethyst | Level 16

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;

                        Obsdate                         tmp_var

                        1 2013-02-02T08:00  02FEB2013:08:00:00
                        2 2013-02-01        01FEB2013:00:00:00

Thanks,

Jagadish

Thanks,
Jag
jakarman
Barite | Level 11

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) 

---->-- ja karman --<-----
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 9254 views
  • 3 likes
  • 3 in conversation