Prev: Windows command line and -termstmt
Next: ODS question
From: Reeza on 9 Oct 2007 14:49
Hi All,

I'm trying to import a CSV file, but the dates formats are different
than expected, ie they are dd/mm/yyyy rather than mm/dd/yyyy and SAS
ends up throwing up errors and truncating the data because of this.

Is there a way to set the default date informat to dd/mm/yyyy easily?

There are 102 variables, with about 20 variables relating to dates.

Thanks,
Reeza

From: tanwan on 9 Oct 2007 23:12
What is your syntax? How does your data look like? I tested PROC
IMPORT with CSV dates as dd/mm/yyyy and mm/dd/yyyy. Trust SAS, the
proc imported these hussle-free, using the proper informat, and even
attaching a format after importing the data!!! Even when I made some
dates without a preceeding 0(zero) like
1/15/1997 or with the zero like 01/15/1997. Perhaps some of your dates
are, strictly speaking, invalid: like 30/02/1990 or 31/11/1990.
Perhaps you know something that I don't.

Tanwan

From: "Howard Schreier <hs AT dc-sug DOT org>" on 10 Oct 2007 12:55
On Tue, 9 Oct 2007 11:49:55 -0700, Reeza <fkhurshed(a)HOTMAIL.COM> wrote:

>Hi All,
>
>I'm trying to import a CSV file, but the dates formats are different
>than expected, ie they are dd/mm/yyyy rather than mm/dd/yyyy and SAS
>ends up throwing up errors and truncating the data because of this.
>
>Is there a way to set the default date informat to dd/mm/yyyy easily?
>
>There are 102 variables, with about 20 variables relating to dates.
>
>Thanks,
>Reeza

My theory is that in the rows which PROC IMPORT pre-scans, it encounters
only dates having day of the month <13; that's ambiguous for purposes of
guessing the right informat.

It seems that the problem could be fixed by coding

options datestyle=DMY;

to reverse the bias derived from the localization of SAS. However, a quick
test (using V. 9.1.3) suggests that this does not work.

Another possible quick fix is to increase the GUESSINGROWS= value.

However, I would suggest eliminating the guesswork. Run PROC IMPORT one more
time and capture the generated code (Recall Last Submit). Then edit the
informats and formats as necessary.
From: "Howard Schreier <hs AT dc-sug DOT org>" on 10 Oct 2007 13:03
On Tue, 9 Oct 2007 20:12:49 -0700, tanwan <tanwanzang(a)YAHOO.COM> wrote:

>What is your syntax? How does your data look like? I tested PROC
>IMPORT with CSV dates as dd/mm/yyyy and mm/dd/yyyy. Trust SAS, the
>proc imported these hussle-free, using the proper informat, and even
>attaching a format after importing the data!!! Even when I made some
>dates without a preceeding 0(zero) like
>1/15/1997 or with the zero like 01/15/1997. Perhaps some of your dates
>are, strictly speaking, invalid: like 30/02/1990 or 31/11/1990.
>Perhaps you know something that I don't.
>
>Tanwan

PROC IMPORT has to do a lot of guessing about data types and attributes. If
you look at the SAS-L archives over the years you will see a steady
procession of problems, especially when the source is a spreadsheet or a
text file. In some situations, an import may work fine one day and have
problems the next, due to nothing but a change in content.
From: Reeza on 10 Oct 2007 17:57
On Oct 10, 10:55 am, nos...(a)HOWLES.COM ("Howard Schreier <hs AT dc-sug
DOT org>") wrote:
> On Tue, 9 Oct 2007 11:49:55 -0700, Reeza <fkhurs...(a)HOTMAIL.COM> wrote:
> >Hi All,
>
> >I'm trying to import a CSV file, but the dates formats are different
> >than expected, ie they are dd/mm/yyyy rather than mm/dd/yyyy and SAS
> >ends up throwing up errors and truncating the data because of this.
>
> >Is there a way to set the default date informat to dd/mm/yyyy easily?
>
> >There are 102 variables, with about 20 variables relating to dates.
>
> >Thanks,
> >Reeza
>
> My theory is that in the rows which PROC IMPORT pre-scans, it encounters
> only dates having day of the month <13; that's ambiguous for purposes of
> guessing the right informat.
>
> It seems that the problem could be fixed by coding
>
> options datestyle=DMY;
>
> to reverse the bias derived from the localization of SAS. However, a quick
> test (using V. 9.1.3) suggests that this does not work.
>
> Another possible quick fix is to increase the GUESSINGROWS= value.
>
> However, I would suggest eliminating the guesswork. Run PROC IMPORT one more
> time and capture the generated code (Recall Last Submit). Then edit the
> informats and formats as necessary.

Thanks, this works, and of course there are other problems so I need
to re request the data from BI.
Reeza

 | 
Pages: 1
Prev: Windows command line and -termstmt
Next: ODS question