topic Re: issue in reading a date. in SAS Programming

issue in reading a date.

Anmolkhandelwal — Thu, 25 Jun 2020 08:14:10 GMT

I have delimeted source file to be read in. there are some date fields with data such as given below.

dat1 dat2

11/14/2018	Nov 14 2
11/15/2018	Nov 15 2
11/15/2018	Nov 15 2

i need to read these values in format mmddyy10.

TIA

Re: issue in reading a date.

Kurt_Bremser — Thu, 25 Jun 2020 08:19:42 GMT

Please post the real contents of the file. Open it with an editor (not with Excel or similar!), and copy/paste the first few lines into a window opened with this button:

Also show us your code, so we can tell you where you went wrong. Use the "little running man" right next to the button indicated above to post the code.

Re: issue in reading a date.

Shmuel — Thu, 25 Jun 2020 08:26:20 GMT

1) What code have you used to read those dates?

Reading dat1 with mmddyy10. informat should result correctly.

Have you defined a format to DAT1 ? (like ddmmyy10., mmddyy10., date9. etc.)

2) Dat2 seems to be truncated.
Please copy paste few lines from the input file into </> icon.

3) In case of any issue it is very helpful to attach the log using the </> icon.

Re: issue in reading a date.

Anmolkhandelwal — Thu, 25 Jun 2020 08:38:09 GMT

SVCDAT ENDDAT
11/14/2018 Nov 14 2
11/15/2018 Nov 15 2
11/15/2018 Nov 15 2
11/17/2018 Nov 17 2
here is my input data.
i have been able to read SVCDAT using mmddyy10. informat.
not able to read the enddat.

Re: issue in reading a date.

Kurt_Bremser — Thu, 25 Jun 2020 08:41:23 GMT

This is not a delimited file; unless the blank is the delimiter, which would mean it has 4 columns instead of 2.

Please read my post again on how to post the REAL contents of the file.

Re: issue in reading a date.

Anmolkhandelwal — Thu, 25 Jun 2020 08:57:14 GMT

DATA sampledata;
     INPUT @6 svcdat mmddyy10. @15 endat mmddyy10.;
    CARDS;
     11/14/2018 nov 2 2;
  ;
RUN;

Here is something i was trying, but the code for endata is completely wrong.

the enddat is present in my input in this type:

Nov 14 2

Nov 15 2

i won't be able to add the source file data due to restrictions.

Re: issue in reading a date.

Kurt_Bremser — Thu, 25 Jun 2020 09:06:17 GMT

@Anmolkhandelwal wrote:
i won't be able to add the source file data due to restrictions.

Without seeing the original data in its raw form (with the real delimiters, and the real layout of the columns), it is impossible to help you.

It is also impossible to make a date out of this:

Nov 14 2

because "2" is not a valid year.

Re: issue in reading a date.

Anmolkhandelwal — Thu, 25 Jun 2020 09:13:04 GMT

Here is a screenshot of the input source file. the two dates are SVCDAT and ENDDAT.

if this may help.

Re: issue in reading a date.

Kurt_Bremser — Thu, 25 Jun 2020 09:18:45 GMT

And to which date should the string "Nov 14 2" translate? What would be the rule for the translation?

To me it seems that somewhere further up in the process a longer string was read into a default 8-character variable, causing truncation of values.

Re: issue in reading a date.

Anmolkhandelwal — Thu, 25 Jun 2020 09:24:21 GMT

and to which date should the string "Nov 14 2" translate? What would be the rule for the translation?
AK: this date translate to enddat. the expected result is to read this date in mmddyy10. format.

Re: issue in reading a date.

Kurt_Bremser — Thu, 25 Jun 2020 09:32:13 GMT

@Anmolkhandelwal wrote:
and to which date should the string "Nov 14 2" translate? What would be the rule for the translation?
AK: this date translate to enddat. the expected result is to read this date in mmddyy10. format.

I did NOT ask for the NAME of the future variable, but for the VALUE that should come out of "Nov 14 2".

Re: issue in reading a date.

Anmolkhandelwal — Thu, 25 Jun 2020 09:39:29 GMT

the value that is expected out of this date is: 11-14-2018.

Re: issue in reading a date.

andreas_lds — Thu, 25 Jun 2020 09:41:14 GMT

@Kurt_Bremser wrote:

@Anmolkhandelwal wrote:
and to which date should the string "Nov 14 2" translate? What would be the rule for the translation?
AK: this date translate to enddat. the expected result is to read this date in mmddyy10. format.

I did NOT ask for the NAME of the future variable, but for the VALUE that should come out of "Nov 14 2".

Well, Kurt, obviously this means '14Nov2'd - or in yyyy-mm-dd: 2-11-14 🤓

Anything else would not make any sense at all or imply heavy guesswork.

Re: issue in reading a date.

Kurt_Bremser — Thu, 25 Jun 2020 09:55:31 GMT

And what should be the result of input string "Jan 1 1"?

Re: issue in reading a date.

Shmuel — Thu, 25 Jun 2020 12:18:06 GMT

@Anmolkhandelwal wrote:

Here is a screenshot of the input source file. the two dates are SVCDAT and ENDDAT.

if this may help.

What is the origin to that input file?

Did you created it or did you got it from someone?

I have the filling that those dates having value like 'Nov 17 2' were truncated or

that those variables are concatenation of more then one variable.

Please check what is origin of those corrupted dates.