Prev: Check if macro parameter is empty
Next: closing excel file opened with dde
From: Peter Crawford on 6 Sep 2006 14:45
toby

As Howard's post pointed out, being correct at the correct time allows
the maximum freedom to allow the alternate delimiter.
In this situation, the delimiter variable could be reset after it
is used in the header and trailers. That avoids having to set it
for every data-row. This wasn't the main concern of my post.
Those were:
dynamic delimiter (covered by Howard already), and
supporting multiple files in the single pass
( headers and trailers among the data )

Peter

DLM reset is needed only after a header has been read --->>
> > if _infile_ =: 'HEADER' then do ;
> > dlm=',' ;
> > input dum ref1 ref2 ;
> > filename= filen ;
> > records= 0 ;
> > dlm='~' ;
> > end;
> > else if _infile_ =: 'TRAILER' then do ;
> > dlm=',' ;
> > input dum TRLR_records trlr_dt ;
> > IF RECORDS ne TRLR_records then put / 'info: ' +1
> > trlr_records= +1 'but' +1 records= +1 'on'
> > (filename ref:)(+1 =) ;
> > end;
> > else /* data */ do ;
> > input MBR_ID SRVC_DT SRC_PD_AMT ;
> > records + 1;
> > output ;
> > end;




> Peter ,
>
> Actually you dont need to specify which delimiter you want with each
> record
> since the only two possibilities are '~' and ','. In my post I mearly
> just
> used DLM = '~,' which uses both depending on which one it finds. The only
> reason I can see for switching the delimiters manually is when you have
> say
> '~' in line one which you want to use as a delimiter and in line 2 you
> want
> '~' treated as text.
>
> If I am wrong in my knowledge please feel free to correct me.
>
>
> Toby Dunn
>
> When everything is coming at you all at once, your in the wrong lane.
>
> A truly happy person is someone who can smile and enjoy the scenery on a
> detour.
>
>
>
>
>
> From: Peter Crawford <peter.crawford(a)BLUEYONDER.CO.UK>
> Reply-To: Peter Crawford <peter.crawford(a)BLUEYONDER.CO.UK>
> To: SAS-L(a)LISTSERV.UGA.EDU
> Subject: Re: input statement with header and trailer
> Date: Wed, 6 Sep 2006 13:44:05 -0400
>
> apologies to original poster Deb Clemens <southfldeb(a)YAHOO.COM>,
> and to the list, for missing out in my earlier response(below),the
> posting from Howard Schreier, when I suggested no prior respondents
> had pointed to the opportunity to reset the infile column delimiter
> during the running of a datastep. Of course Howard did point to that
> facility and did demonstrate that capability.
>
> Peter Crawford
>
>
> On Wed, 6 Sep 2006 08:10:49 -0400, Peter Crawford
> <peter.crawford(a)BLUEYONDER.CO.UK> wrote:
>
> >On Tue, 5 Sep 2006 11:40:33 -0400, Deb Clemens <southfldeb(a)YAHOO.COM>
> >wrote:
> >
> >>I have mulitple files that look like this:
> >>
> >>HEADER,999,posfile
> >>JAMES~10/16/1987~12.56
> >>DEB~11/01/1985~13.75
> >>......
> >>MATT~11/05/1996~11.42
> >>TRAILER,386987,09/05/2006
> >>
> >>the delimeter in the header and trailer is a comma and the for the data
> is
> >>the atilda (~).
> >>
> >>Data UHC.UHC_RX;
> >>infile 'C:\Public Sector\posfile\file?.csv'
> >>delimiter='~'
> >>missover
> >>firstobs=2
> >>end=TRAILER;
> >>input
> >> MBR_ID : $25.
> >> SRVC_DT : mmddyy10.
> >> SRC_PD_AMT : 9.2
> >>;
> >>format srvc_dt mmddyy10. SRC_PD_AMT Dollar12.2;
> >>RUN;
> >>
> >>the code is not really workingfor me. I am getting lost card errors. I
> >>would like to keep the trailer record so I can verify the number of
> >>records with it.
> >>
> >>suggestions appreciated.
> >
> >Deb
> >
> >sorry to be late "on the case" ;-)
> >
> >Your respondents, so far have missed a facility that is very
> >convenient in this situation. Infile statements allow the delimiter
> >to be (re)defined during the run.
> >This allows your code to simplify, as follows:
> >
> >Data UHC.UHC_RX;
> > retain dlm ',' filen filename records ref1-ref2 ;
> > length filen filename ref1-ref2 $100 mbr_id $25 dum $1 ;
> > format SRC_PD_AMT Dollar12.2 srvc_dt mmddyy10. ;
> > informat trlr_dt srvc_dt mmddyy10. ;
> >
> > infile 'C:\Public Sector\posfile\file?.csv'
> > /* having that ? implies multiple header/trailers, so I
> > drop handling the very last and added filename= */
> > filename= filen /* make filename available in the step */
> > delimiter= dlm DSD truncover firstobs=2 ;
> > input @ ;
> > if _infile_ =: 'HEADER' then do ;
> > dlm=',' ;
> > input dum ref1 ref2 ;
> > filename= filen ;
> > records= 0 ;
> > end;
> > else if _infile_ =: 'TRAILER' then do ;
> > dlm=',' ;
> > input dum TRLR_records trlr_dt ;
> > IF RECORDS ne TRLR_records then put / 'info: ' +1
> > trlr_records= +1 'but' +1 records= +1 'on'
> > (filename ref:)(+1 =) ;
> > end;
> > else /* data */ do ;
> > dlm='~' ;
> > input MBR_ID SRVC_DT SRC_PD_AMT ;
> > records + 1;
> > output ;
> > end;
> >RUN;
> >
> >(well, something like that... after your testing )
> >
> >Good Luck
> >
> >Peter Crawford
> >
> >P.S.
> >that trailer count looks very high?
>
>
>
>


--
Peter Crawford
Peter.Crawford(a)blueyonder.co.uk
From: debbie weiss on 6 Sep 2006 14:45
As is with any problem there are mulitple ways to attack it and get the desired results. thanks for the mulitple responses.

I ended up using the code first presented because, well it was first.

In my orginal program I did not understand the "eof=whatever" concept. I like Howard Schreier method. It looks like it will be efficient. Especially since I am reading in over 3 million records and fast is always better.

I am reading 8 files and used the ? as a wildcard to read them in together. The first obs=2 only worked for the first file. I ended up using and @ with an "If then else" statement

Here is my not very sophisitcated code;

Data RX(DROP = ENTITY FILENAME RECORDS CREATEDATE)
headerTrailerinfo(KEEP=DROP = ENTITY FILENAME RECORDS CREATEDATE);
infile 'C:\rxclaim_file?.csv'
dlm='~,'
TRUNCOVER
;
input @1 CLM_AUD_NBR : $25. @;
if CLM_AUD_NBR ='HEADER' THEN do;
input entity $ filename $;
OUTPUT headerTrailerinfo;
PUT FILENAME;
end;
if CLM_AUD_NBR ='TRAILER' THEN do;
input RECORDS :8. CREATEDATE : mmddyy10.;
OUTPUT headerTrailerinfo;
PUT RECORDS;
end;
ELSE DO;
input
@1 CLM_AUD_NBR : $25.
MBR_ID : $35.
ERLY_SRVC_DT : mmddyy10.
NDC_CD : $12.
DIAG_1_CD : $10.
SRC_PD_AMT : 9.2
;
format Erly_srvc_dt mmddyy10. SRC_PD_AMT Dollar12.2;
AMA_PL_OF_SRVC_CD='RX';
OUTPUT RX ;
END;
RUN;


---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
First  |  Prev  | 
Pages: 1 2
Prev: Check if macro parameter is empty
Next: closing excel file opened with dde