LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (December 2009, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.


Back to: Top of message | Previous page | Main SAS-L page