Date:         Sat, 19 Dec 2009 08:44:26 -0800
Reply-To:     xlr82sas <xlr82sas@AOL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         xlr82sas <xlr82sas@AOL.COM>
Organization: http://groups.google.com
Subject:      Re: Import delimited (.txt) file -- URGENT PLEASE
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On Dec 19, 11:39 am, xlr82sas <xlr82...@aol.com> wrote:
> On Dec 18, 11:32 pm, mlhow...@avalon.net (Mary) wrote:
>
>
>
>
>
> > Tom,
>
> > It would really help if you would post your code that you are attempting to import with; here is an example of import code with a pipe-delimited file; note the infile statement is using lrecl of 32000+ and missover and dsd.
>
> > data WORK.lipitor3q08;
> > informat
> >         DAY yymmdd10.
> >         MONTH $7.
> >         YEAR $4.
> >         QUARTER $6.
> >     PAT_INDV_ID best32.
> >  PAT_BIRTH_DT yymmdd10.
> >  RETAIL_MAILORDR_CD $10.
> >  BRAND_NM $40.
> >  DOSAGE_FORM_DESCR $40.
> >  DRUG_STR_DESCR $40.
> >  PROD_UHC_CD $3.
> >  FUND_IND $1.
> >  INFERRED_FILL_QTY best32.
> >  DAYS_SUPPLIED best32.
> >  COPAY_AMT best32.
> >  DED_AMT best32.
> >  ANCILLARY_AMT best32.
> >  PAID_AMT best32.
> >  RET_FLAT_IND_TIER_1 $1.
> >  RET_FLAT_IND_TIER_2 $1.
> >  RET_FLAT_IND_TIER_3 $1.
> >  RET_FLAT_IND_TIER_4 $1.
> >  COPAY_RETAIL_FLAT_TIER_1 best32.
> >  COPAY_RETAIL_FLAT_TIER_2 best32.
> >  COPAY_RETAIL_FLAT_TIER_3 best32.
> >  COPAY_RETAIL_FLAT_TIER_4 best32.
> >  COPAY_RETAIL_PCT_TIER_1 best32.
> >  COPAY_RETAIL_PCT_TIER_2 best32.
> >  COPAY_RETAIL_PCT_TIER_3 best32.
> >  COPAY_RETAIL_PCT_TIER_4 best32.
> >  RET_PCT_IND_TIER_1 $1.
> >  RET_PCT_IND_TIER_2 $1.
> >  RET_PCT_IND_TIER_3 $1.
> >  RET_PCT_IND_TIER_4 $1.
> >  CARRIER_NBR $4.
> >  BENEFIT_PLAN_NBR $15.
> >  AGE_NBR best32.
> >  PRSC_SPCLTY $20.
> >  PRSC_SPCLTY_NM $40.
> >  LEVELX  $40.
> >  GOPHER_DT $40.
> >  UNKNOWN_FLG $40.
> >  PROD_DERIVED_KEY $40.
> >  PROD_UHC_KEY $40.
> >  PROD_RPTG_GRP_KEY $40.;
> > format
> > day yymmdd10. pat_birth_dt yymmdd10.;
>
> > infile 'P:\MCA Projects\UHPS\Outcomes and Research\mary_howard\mh_2009_11_02_lipitor\saslib\lipitor_3q08_2009_11_02.o ut'
> > delimiter = '|'
> > MISSOVER DSD lrecl=32767 firstobs=2 ;
> > input
> > DAY
> > MONTH
> > YEAR
> > QUARTER
> > PAT_INDV_ID
> > PAT_BIRTH_DT
> >  RETAIL_MAILORDR_CD
> >  BRAND_NM
> >  DOSAGE_FORM_DESCR
> >  DRUG_STR_DESCR
> >  PROD_UHC_CD
> >  FUND_IND
> >  INFERRED_FILL_QTY
> >  DAYS_SUPPLIED
> >  COPAY_AMT
> >  DED_AMT
> >  ANCILLARY_AMT
> >  PAID_AMT
> >  RET_FLAT_IND_TIER_1
> >  RET_FLAT_IND_TIER_2
> >  RET_FLAT_IND_TIER_3
> >  RET_FLAT_IND_TIER_4
> >  COPAY_RETAIL_FLAT_TIER_1
> >  COPAY_RETAIL_FLAT_TIER_2
> >  COPAY_RETAIL_FLAT_TIER_3
> >  COPAY_RETAIL_FLAT_TIER_4
> >  COPAY_RETAIL_PCT_TIER_1
> >  COPAY_RETAIL_PCT_TIER_2
> >  COPAY_RETAIL_PCT_TIER_3
> >  COPAY_RETAIL_PCT_TIER_4
> >  RET_PCT_IND_TIER_1
> >  RET_PCT_IND_TIER_2
> >  RET_PCT_IND_TIER_3
> >  RET_PCT_IND_TIER_4
> >  CARRIER_NBR
> >  BENEFIT_PLAN_NBR
> >  AGE_NBR
> >  PRSC_SPCLTY
> >  PRSC_SPCLTY_NM
> >  LEVELX
> >  GOPHER_DT
> >  UNKNOWN_FLG
> >  PROD_DERIVED_KEY
> >  PROD_UHC_KEY
> >  PROD_RPTG_GRP_KEY
> > ;
> > run;
>
> > --- t...@MAIL.COM wrote:
>
> > From: Tom White <t...@MAIL.COM>
> > To: SA...@LISTSERV.UGA.EDU
> > Subject:      Re: Import delimited (.txt) file -- URGENT PLEASE
> > Date:         Fri, 18 Dec 2009 15:28:42 -0500
>
> > 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 <KevinMy...@austin.rr.com>
> > To: t...@MAIL.COM; SA...@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" <KevinMy...@austin.rr.com>
> > To: <t...@MAIL.COM>; <SA...@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" <t...@MAIL.COM>
> > > To: <SA...@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;
>
> Hi,
>
> Just some thoughts.
>
> On holiday with no SAS so the code below probably has issues
>
> 1.  set ignore end of file DOS char(I forgot the exact option)
> 2. try
> filename txt "c:\txt.txt" recfm=v lrecl=32756;
> filename fix "c:\newtxt.txt" recfm=v lrecl=32756";
> data txt;
>   file fix;
>   lenght str org $32756;
>    infile txt length=l;
>   input;
>   org=_infile_;
>   str=_infile_;
>   put str;
>   str=compress(str,,'ka'); /* not sure ka is keep alpha may need kp
> for keep printable */
>   pipes=countc('!');
> run;
>
> post processing
> compare str and fix
> use file fix
>
> You can also eliminate the _infile_ by using a user named infile, but
> I forgot the construct.

You should add one more line to the program
len=l;
so you can see the length of each line.