Hello SAS-L,

I have a delimited (|) .txt file with about 300K obs.

I am trying to import it into SAS withy partial success.

I can read in the file fine, bit it is dropping many thousands of records=
 (Lost Card).
Of the 300K obs it brings in only about 40K.

When I bring in only NAME it reads all 300K obs.
When I bring in both NAME and LAST t brings in all 300K obs.
When I bring in CITY it drops one obs.
When I progressively bring in more ad more fields, i.e. read in the file=
 in its entirety,
I have lost most of the obs.
The file has about 300 character fields.
I only show 3 below in the code I am using.
Please HELP.
Thank you.

data foo;
length
NAME
LAST
CITY
..
..
..

;
infile "/.../MYFILE.txt" DLM=3D'|' DSD;
input
NAME
LAST
CITY
..
..
..
'
run;

Two possibilities:

1. Some records don't have data for all variables.  Try adding the MISSOVER,
TRUNCOVER, or FLOWOVER options to your infile statement, as appropriate.
2. Some records are longer than the default record length for your system.
Try adding the LRECL and/or LINESIZE options to your infile statement, and
set to a very large value.

HTH,
s/KAM


----- Original Message -----
From: "Tom White" <tw2@MAIL.COM>
To: <SAS-L@LISTSERV.UGA.EDU>
Sent: Friday, December 18, 2009 12:50
Subject: Import delimited (.txt) file -- URGENT PLEASE


Hello SAS-L,

I have a delimited (|) .txt file with about 300K obs.

I am trying to import it into SAS withy partial success.

I can read in the file fine, bit it is dropping many thousands of records
(Lost Card).
Of the 300K obs it brings in only about 40K.

When I bring in only NAME it reads all 300K obs.
When I bring in both NAME and LAST t brings in all 300K obs.
When I bring in CITY it drops one obs.
When I progressively bring in more ad more fields, i.e. read in the file in
its entirety,
I have lost most of the obs.
The file has about 300 character fields.
I only show 3 below in the code I am using.
Please HELP.
Thank you.

data foo;
length
NAME
LAST
CITY
..
..
..

;
infile "/.../MYFILE.txt" DLM='|' DSD;
input
NAME
LAST
CITY
..
..
..
'
run;

P.S. - More than likely, setting LRECL is all you need.

----- Original Message -----
From: "Kevin Myers" <KevinMyers@austin.rr.com>
To: <tw2@MAIL.COM>; <SAS-L@LISTSERV.UGA.EDU>
Sent: Friday, December 18, 2009 13:00
Subject: Re: Import delimited (.txt) file -- URGENT PLEASE


> Two possibilities:
>
> 1. Some records don't have data for all variables.  Try adding the
> MISSOVER, TRUNCOVER, or FLOWOVER options to your infile statement, as
> appropriate.
> 2. Some records are longer than the default record length for your system.
> Try adding the LRECL and/or LINESIZE options to your infile statement, and
> set to a very large value.
>
> HTH,
> s/KAM
>
>
> ----- Original Message -----
> From: "Tom White" <tw2@MAIL.COM>
> To: <SAS-L@LISTSERV.UGA.EDU>
> Sent: Friday, December 18, 2009 12:50
> Subject: Import delimited (.txt) file -- URGENT PLEASE
>
>
> Hello SAS-L,
>
> I have a delimited (|) .txt file with about 300K obs.
>
> I am trying to import it into SAS withy partial success.
>
> I can read in the file fine, bit it is dropping many thousands of records
> (Lost Card).
> Of the 300K obs it brings in only about 40K.
>
> When I bring in only NAME it reads all 300K obs.
> When I bring in both NAME and LAST t brings in all 300K obs.
> When I bring in CITY it drops one obs.
> When I progressively bring in more ad more fields, i.e. read in the file
> in its entirety,
> I have lost most of the obs.
> The file has about 300 character fields.
> I only show 3 below in the code I am using.
> Please HELP.
> Thank you.
>
> data foo;
> length
> NAME
> LAST
> CITY
> .
> .
> .
>
> ;
> infile "/.../MYFILE.txt" DLM='|' DSD;
> input
> NAME
> LAST
> CITY
> .
> .
> .
> '
> run;
>

Is it possible that some of the fields actually contain your delimiter =
character?  If so, that's a very tough problem to solve, usually =
requires some really ugly code, and even then often isn't perfect.

Other than that, all I can suggest is to post some or all of the records =
that fail to load somewhere on the net, and maybe we will be able to =
help you figure it out.
  ----- Original Message -----=20
  From: tw2@mail.com=20
  To: KevinMyers@austin.rr.com ; sas-l@listserv.uga.edu=20
  Sent: Friday, December 18, 2009 14:28
  Subject: Re: Import delimited (.txt) file -- URGENT PLEASE



  Kevin,

  Thank you for you suggestions but I cant's get it to work

  When I try in the infile the optio(s):

  DSD alone brings in only about 40k (of the 300k) but all fields =
populated.
  DSD FLOWOVER brings in only about 40k (of the 300k) but all fields =
populated.
  DSD MISSOVER brings in all 300k but all fields not populated.
  DSD TRUNCOVER brings in all 300k but all fields not populated.

  DSD MISSOVER TRUNCOVER brings in all 300k but all fields not =
populated.
  DSD MISSOVER FLOWOVER brings in all 300k but all fields not populated.
  DSD MISSOVER TRUNCOVER FLOWOVER brings in all 300k but all fields not =
populated.

  DSD LRECL=3D1000 brings in a few more 60k (of the 300k) but all fields =
populated.
  DSD LRECL=3D1000 FLOWOVER brings in a few more 60k (of the 300k) but =
all fields populated.

  Adding MISSOVER or TRUNCOVER or both to the last statement brings in =
all 300k but all fields not populated.

  DSD LRECL=3D1000 MISSOVER TRUNCOVER brings in all 300k but all fields =
not populated.

  Any suggestions please ?

  Thank you.

  Tom








  =20
  -----Original Message-----
  From: Kevin Myers <KevinMyers@austin.rr.com>
  To: tw2@MAIL.COM; SAS-L@LISTSERV.UGA.EDU
  Sent: Fri, Dec 18, 2009 1:02 pm
  Subject: Re: Import delimited (.txt) file -- URGENT PLEASE


  P.S. - More than likely, setting LRECL is all you need.=20
  =20
  ----- Original Message ----- From: "Kevin Myers" =
<KevinMyers@austin.rr.com>=20
  To: <tw2@MAIL.COM>; <SAS-L@LISTSERV.UGA.EDU>=20
  Sent: Friday, December 18, 2009 13:00=20
  Subject: Re: Import delimited (.txt) file -- URGENT PLEASE=20
  =20
  > Two possibilities:=20
  >=20
  > 1. Some records don't have data for all variables. Try adding the > =
MISSOVER, TRUNCOVER, or FLOWOVER options to your infile statement, as > =
appropriate.=20
  > 2. Some records are longer than the default record length for your =
system. > Try adding the LRECL and/or LINESIZE options to your infile =
statement, and > set to a very large value.=20
  >=20
  > HTH,=20
  > s/KAM=20
  >=20
  >=20
  > ----- Original Message ----- > From: "Tom White" <tw2@MAIL.COM>=20
  > To: <SAS-L@LISTSERV.UGA.EDU>=20
  > Sent: Friday, December 18, 2009 12:50=20
  > Subject: Import delimited (.txt) file -- URGENT PLEASE=20
  >=20
  >=20
  > Hello SAS-L,=20
  >=20
  > I have a delimited (|) .txt file with about 300K obs.=20
  >=20
  > I am trying to import it into SAS withy partial success.=20
  >=20
  > I can read in the file fine, bit it is dropping many thousands of =
records > (Lost Card).=20
  > Of the 300K obs it brings in only about 40K.=20
  >=20
  > When I bring in only NAME it reads all 300K obs.=20
  > When I bring in both NAME and LAST t brings in all 300K obs.=20
  > When I bring in CITY it drops one obs.=20
  > When I progressively bring in more ad more fields, i.e. read in the =
file > in its entirety,=20
  > I have lost most of the obs.=20
  > The file has about 300 character fields.=20
  > I only show 3 below in the code I am using.=20
  > Please HELP.=20
  > Thank you.=20
  >=20
  > data foo;=20
  > length=20
  > NAME=20
  > LAST=20
  > CITY=20
  > .=20
  > .=20
  > .=20
  >=20
  > ;=20
  > infile "/.../MYFILE.txt" DLM=3D'|' DSD;=20
  > input=20
  > NAME=20
  > LAST=20
  > CITY=20
  > .=20
  > .=20
  > .=20
  > '=20
  > run;=20
  > =20

The fact that MISSOVER and TRUNCOVER read all records suggests that an =
embedded DOS EOF marker is not the problem.
However, DOS (or *nix, what platform are you on?) end of line markers =
embedded in the data could cause this behavior.

Have you manually reviewed some of the offending records in a hex editor =
to see exactly what they *really* contain?
When you say all fields not populated, are the unpopulated fields always =
at the end of the records?

The fact that adding LRECL=3D1000 causes more records to be read =
indicates that your records ARE longer than the default, and that you DO =
need to use the LRECL option.  However, are you certain that =
LRECL=3D1000 is adequate?  Maybe you should try LRECL=3D32767 just to be =
sure...
300 fields only need to exceed an average of 3 characters per field in =
order to exceed a LRECL of 1000, so I bet this is your problem.

  ----- Original Message -----=20
  From: tw2@mail.com=20
  To: KevinMyers@austin.rr.com ; sas-l@listserv.uga.edu=20
  Sent: Friday, December 18, 2009 14:28
  Subject: Re: Import delimited (.txt) file -- URGENT PLEASE



  Kevin,

  Thank you for you suggestions but I cant's get it to work

  When I try in the infile the optio(s):

  DSD alone brings in only about 40k (of the 300k) but all fields =
populated.
  DSD FLOWOVER brings in only about 40k (of the 300k) but all fields =
populated.
  DSD MISSOVER brings in all 300k but all fields not populated.
  DSD TRUNCOVER brings in all 300k but all fields not populated.

  DSD MISSOVER TRUNCOVER brings in all 300k but all fields not =
populated.
  DSD MISSOVER FLOWOVER brings in all 300k but all fields not populated.
  DSD MISSOVER TRUNCOVER FLOWOVER brings in all 300k but all fields not =
populated.

  DSD LRECL=3D1000 brings in a few more 60k (of the 300k) but all fields =
populated.
  DSD LRECL=3D1000 FLOWOVER brings in a few more 60k (of the 300k) but =
all fields populated.

  Adding MISSOVER or TRUNCOVER or both to the last statement brings in =
all 300k but all fields not populated.

  DSD LRECL=3D1000 MISSOVER TRUNCOVER brings in all 300k but all fields =
not populated.

  Any suggestions please ?

  Thank you.

  Tom








  =20
  -----Original Message-----
  From: Kevin Myers <KevinMyers@austin.rr.com>
  To: tw2@MAIL.COM; SAS-L@LISTSERV.UGA.EDU
  Sent: Fri, Dec 18, 2009 1:02 pm
  Subject: Re: Import delimited (.txt) file -- URGENT PLEASE


  P.S. - More than likely, setting LRECL is all you need.=20
  =20
  ----- Original Message ----- From: "Kevin Myers" =
<KevinMyers@austin.rr.com>=20
  To: <tw2@MAIL.COM>; <SAS-L@LISTSERV.UGA.EDU>=20
  Sent: Friday, December 18, 2009 13:00=20
  Subject: Re: Import delimited (.txt) file -- URGENT PLEASE=20
  =20
  > Two possibilities:=20
  >=20
  > 1. Some records don't have data for all variables. Try adding the > =
MISSOVER, TRUNCOVER, or FLOWOVER options to your infile statement, as > =
appropriate.=20
  > 2. Some records are longer than the default record length for your =
system. > Try adding the LRECL and/or LINESIZE options to your infile =
statement, and > set to a very large value.=20
  >=20
  > HTH,=20
  > s/KAM=20
  >=20
  >=20
  > ----- Original Message ----- > From: "Tom White" <tw2@MAIL.COM>=20
  > To: <SAS-L@LISTSERV.UGA.EDU>=20
  > Sent: Friday, December 18, 2009 12:50=20
  > Subject: Import delimited (.txt) file -- URGENT PLEASE=20
  >=20
  >=20
  > Hello SAS-L,=20
  >=20
  > I have a delimited (|) .txt file with about 300K obs.=20
  >=20
  > I am trying to import it into SAS withy partial success.=20
  >=20
  > I can read in the file fine, bit it is dropping many thousands of =
records > (Lost Card).=20
  > Of the 300K obs it brings in only about 40K.=20
  >=20
  > When I bring in only NAME it reads all 300K obs.=20
  > When I bring in both NAME and LAST t brings in all 300K obs.=20
  > When I bring in CITY it drops one obs.=20
  > When I progressively bring in more ad more fields, i.e. read in the =
file > in its entirety,=20
  > I have lost most of the obs.=20
  > The file has about 300 character fields.=20
  > I only show 3 below in the code I am using.=20
  > Please HELP.=20
  > Thank you.=20
  >=20
  > data foo;=20
  > length=20
  > NAME=20
  > LAST=20
  > CITY=20
  > .=20
  > .=20
  > .=20
  >=20
  > ;=20
  > infile "/.../MYFILE.txt" DLM=3D'|' DSD;=20
  > input=20
  > NAME=20
  > LAST=20
  > CITY=20
  > .=20
  > .=20
  > .=20
  > '=20
  > run;=20
  > =20

----- Original Message -----
From: "Kevin Myers" <KevinMyers@AUSTIN.RR.COM>
To: <SAS-L@LISTSERV.UGA.EDU>
Sent: Friday, December 18, 2009 16:19
Subject: Re: Import delimited (.txt) file -- URGENT PLEASE


The fact that MISSOVER and TRUNCOVER read all records suggests that an
embedded DOS EOF marker is not the problem.
However, DOS (or *nix, what platform are you on?) end of line markers
embedded in the data could cause this behavior.

Have you manually reviewed some of the offending records in a hex editor to
see exactly what they *really* contain?
When you say all fields not populated, are the unpopulated fields always at
the end of the records?

The fact that adding LRECL=1000 causes more records to be read indicates
that your records ARE longer than the default, and that you DO need to use
the LRECL option.  However, are you certain that LRECL=1000 is adequate?
Maybe you should try LRECL=32767 just to be sure...
300 fields only need to exceed an average of 3 characters per field in order
to exceed a LRECL of 1000, so I bet this is your problem.

  ----- Original Message -----
  From: tw2@mail.com
  To: KevinMyers@austin.rr.com ; sas-l@listserv.uga.edu
  Sent: Friday, December 18, 2009 14:28
  Subject: Re: Import delimited (.txt) file -- URGENT PLEASE



  Kevin,

  Thank you for you suggestions but I cant's get it to work

  When I try in the infile the optio(s):

  DSD alone brings in only about 40k (of the 300k) but all fields populated.
  DSD FLOWOVER brings in only about 40k (of the 300k) but all fields
populated.
  DSD MISSOVER brings in all 300k but all fields not populated.
  DSD TRUNCOVER brings in all 300k but all fields not populated.

  DSD MISSOVER TRUNCOVER brings in all 300k but all fields not populated.
  DSD MISSOVER FLOWOVER brings in all 300k but all fields not populated.
  DSD MISSOVER TRUNCOVER FLOWOVER brings in all 300k but all fields not
populated.

  DSD LRECL=1000 brings in a few more 60k (of the 300k) but all fields
populated.
  DSD LRECL=1000 FLOWOVER brings in a few more 60k (of the 300k) but all
fields populated.

  Adding MISSOVER or TRUNCOVER or both to the last statement brings in all
300k but all fields not populated.

  DSD LRECL=1000 MISSOVER TRUNCOVER brings in all 300k but all fields not
populated.

  Any suggestions please ?

  Thank you.

  Tom









  -----Original Message-----
  From: Kevin Myers <KevinMyers@austin.rr.com>
  To: tw2@MAIL.COM; SAS-L@LISTSERV.UGA.EDU
  Sent: Fri, Dec 18, 2009 1:02 pm
  Subject: Re: Import delimited (.txt) file -- URGENT PLEASE


  P.S. - More than likely, setting LRECL is all you need.

  ----- Original Message ----- From: "Kevin Myers"
<KevinMyers@austin.rr.com>
  To: <tw2@MAIL.COM>; <SAS-L@LISTSERV.UGA.EDU>
  Sent: Friday, December 18, 2009 13:00
  Subject: Re: Import delimited (.txt) file -- URGENT PLEASE

  > Two possibilities:
  >
  > 1. Some records don't have data for all variables. Try adding the >
MISSOVER, TRUNCOVER, or FLOWOVER options to your infile statement, as >
appropriate.
  > 2. Some records are longer than the default record length for your
system. > Try adding the LRECL and/or LINESIZE options to your infile
statement, and > set to a very large value.
  >
  > HTH,
  > s/KAM
  >
  >
  > ----- Original Message ----- > From: "Tom White" <tw2@MAIL.COM>
  > To: <SAS-L@LISTSERV.UGA.EDU>
  > Sent: Friday, December 18, 2009 12:50
  > Subject: Import delimited (.txt) file -- URGENT PLEASE
  >
  >
  > Hello SAS-L,
  >
  > I have a delimited (|) .txt file with about 300K obs.
  >
  > I am trying to import it into SAS withy partial success.
  >
  > I can read in the file fine, bit it is dropping many thousands of
records > (Lost Card).
  > Of the 300K obs it brings in only about 40K.
  >
  > When I bring in only NAME it reads all 300K obs.
  > When I bring in both NAME and LAST t brings in all 300K obs.
  > When I bring in CITY it drops one obs.
  > When I progressively bring in more ad more fields, i.e. read in the file
 > in its entirety,
  > I have lost most of the obs.
  > The file has about 300 character fields.
  > I only show 3 below in the code I am using.
  > Please HELP.
  > Thank you.
  >
  > data foo;
  > length
  > NAME
  > LAST
  > CITY
  > .
  > .
  > .
  >
  > ;
  > infile "/.../MYFILE.txt" DLM='|' DSD;
  > input
  > NAME
  > LAST
  > CITY
  > .
  > .
  > .
  > '
  > run;
  >