input statement with header and trailer
in [SAS]
|
From: toby dunn on 5 Sep 2006 12:13 Deb , This should give you a starting place: ata Need ; Infile Cards DLM = '~,' ; Input MBR_ID $ @ ; If MBR_ID = 'HEADER' Then Do ; Input Records Text $ ; End ; Else If MBR_ID = 'TRAILER' Then Do ; Input Records Date mmddyy10. ; End ; Else Do ; Input SRVC_DT : mmddyy10. SRC_PD_AMT : 9.2 ; End ; Cards ; 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 ; Run ; Proc Print Data = Need ; Run ; 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: Deb Clemens <southfldeb(a)YAHOO.COM> Reply-To: Deb Clemens <southfldeb(a)YAHOO.COM> To: SAS-L(a)LISTSERV.UGA.EDU Subject: input statement with header and trailer Date: Tue, 5 Sep 2006 11:40:33 -0400 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.
From: "Howard Schreier <hs AT dc-sug DOT org>" on 5 Sep 2006 21:45 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. "Lost Card" isn't actually an error, but it is a problem indicator. You are creating a flag with the END= option, but then not doing anything with it. Try something along these lines: filename demo 'c:\temp\demo'; data _null_; file demo; put 'HEADER,999,posfile'; put 'JAMES~10/16/1987~12.56'; put 'DEB~11/01/1985~13.75'; put 'MATT~11/05/1996~11.42'; put 'TRAILER,386987,09/05/2006'; run; Data UHC_RX(drop = count : ); infile demo delimiter=delim missover firstobs=2 end=lastrecord; input @; if lastrecord then do; delim = ','; input @ ',' count_expected; put count_expected ' expected, ' count ' actually read'; end; else do; delim = '~'; input MBR_ID : $25. SRVC_DT : mmddyy10. SRC_PD_AMT : 9.2 ; output; count + 1; end; format srvc_dt mmddyy10. SRC_PD_AMT Dollar12.2; run; You could probably get by with delimiter=',~', but the way I did it is a bit more precise and allows for the possibility of commas within tilde-separated fields and vice versa. The first INPUT statement, with the "@", reads a record and holds the buffer, it allows the program to branch before actually scanning any fields. Log shows "386987 expected, 3 actually read".
From: Peter Crawford on 6 Sep 2006 08:10 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?
From: Peter Crawford on 6 Sep 2006 13:44 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?
From: toby dunn on 6 Sep 2006 14:00 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?
|
Next
|
Last
Pages: 1 2 Prev: Check if macro parameter is empty Next: closing excel file opened with dde |