Infile Statment Help

[Ahsan]

Hi,

I try to use infile statement here is my code but I got this error message

DATA test;

LENGTH

'EFF DTE'n 8

COMP 8

ACCOUNT 8

'ACCOUNT DESCRIPTION'n $ 24

CENTER 8

'DOC ID'n $ 12

LINE 8

'INV.DTE'n 8

ENTY $ 4

'TXN DESCRIPTION'n $ 21

'CHECK NUM'n 8

'CTRL D GRP'n 8

AMOUNT 8

SY $ 2

'VENDOR N.'n 8

GR 8

BUYR $ 4

'PO NUMBER'n $ 10 ;

FORMAT

'EFF DTE'n MMDDYY10.

COMP BEST4.

ACCOUNT BEST6.

'ACCOUNT DESCRIPTION'n $CHAR24.

CENTER BEST8.

'DOC ID'n $CHAR12.

LINE BEST4.

'INV.DTE'n MMDDYY10.

ENTY $CHAR4.

'TXN DESCRIPTION'n $CHAR21.

'CHECK NUM'n BEST11.

'CTRL D GRP'n BEST11.

AMOUNT BEST5.

SY $CHAR2.

'VENDOR N.'n BEST9.

GR BEST2.

BUYR $CHAR4.

'PO NUMBER'n $CHAR10. ;

INFORMAT

'EFF DTE'n MMDDYY10.

COMP BEST4.

ACCOUNT BEST6.

'ACCOUNT DESCRIPTION'n $CHAR24.

CENTER BEST8.

'DOC ID'n $CHAR12.

LINE BEST4.

'INV.DTE'n MMDDYY10.

ENTY $CHAR4.

'TXN DESCRIPTION'n $CHAR21.

'CHECK NUM'n BEST11.

'CTRL D GRP'n BEST11.

AMOUNT BEST5.

SY $CHAR2.

'VENDOR N.'n BEST9.

GR BEST2.

BUYR $CHAR4.

'PO NUMBER'n $CHAR10. ;

INFILE 'H:\TEST_INFILE\OIA_EXCEL.txt'

LRECL=170

ENCODING="WLATIN1"

TERMSTR=CRLF

DLM='7F'x

MISSOVER

DSD ;

INPUT

'EFF DTE'n : ?? MMDDYY8.

COMP : ?? BEST4.

ACCOUNT : ?? BEST6.

'ACCOUNT DESCRIPTION'n : $CHAR24.

CENTER : ?? BEST8.

'DOC ID'n : $CHAR12.

LINE : ?? BEST4.

'INV.DTE'n : ?? MMDDYY8.

ENTY : $CHAR4.

'TXN DESCRIPTION'n : $CHAR21.

'CHECK NUM'n : ?? BEST11.

'CTRL D GRP'n : ?? BEST11.

AMOUNT : ?? COMMA5.

SY : $CHAR2.

'VENDOR N.'n : ?? BEST9.

GR : ?? BEST2.

BUYR : $CHAR4.

'PO NUMBER'n : $CHAR10. ;

RUN;

That's the error message

ERROR: Physical file does not exist, H:\TEST_INFILE\OIA_EXCEL.txt.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.TEST may be incomplete. When this step was stopped there were 0 observations and 18 variables.

WARNING: Data set WORK.TEST was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.23 seconds

cpu time 0.07 seconds

[Reeza]

ERROR: Physical file does not exist, H:\TEST_INFILE\OIA_EXCEL.txt.

Is the file in that location? 
Navigate to the location and right click the file while holding down the ALT button. Select Copy as Path and use that path in your code.

[Tom]

The error message seems pretty clear. You have either typed the location of the file wrong, or the system where SAS is running cannot access that location.  Are you running SAS on a server?  Via Enterprise Guide or SAS/Studio interface perhaps?

But why did you attach all of those informats?  And why did you specify them twice, both with an INFORMAT statement and in-line in the INPUT statement?  

Note that SAS already knows how to read numbers and character strings.  Unlike with formats there is no actual informat named BEST, If you as for it SAS will simple use the normal numeric informat.  In some ways COMMA is a better informat to use as the "BEST" informat since it will recognize more strings that humans would classify as numbers.  Also the only impact of adding the $CHAR informat will be to preserve leading spaces.  Does your data file even have any values with leading spaces?  And did you really want to keep them?

If you want to suppress all of the invalid value messages you can just include the ?? in the INPUT statement without  also adding an informat specification.

[Ahsan]

When I use import wizard in sas eg it imported successfully but when I use infile statement it's physical file does not exist.

[Reeza]

Are you running EG on a server or locally?
Can you show the full log?

[Ahsan]

right now I am running locally and it still giving me error

[ChrisHemedinger]

Check the steps I've outlined here for adapting Import Data task code to your own production process.

And if your SAS server is remote, you might not have access to that H: drive -- at least, not by the same path reference.  EG's task copies the file to the server for you.  In your program, you either need to reference it differently or copy the text file as a separate step (see above blog post).

[Kurt_Bremser]

I guess @Reeza wants to know if you connect from EG to the "Local" server or a remote server (usually "SASApp").

[Ahsan]

I attached the txt file which I need to import using infile statement.

[Reeza]

It sounds like the file isn't the issue, access is the file, so the file isn't required. You do need to post the full log though.

[Ahsan]

I have attached the log.

[Reeza]

Did you try the copy path technique I mentioned? I think I said, ALT but it's really the SHIFT key instead.

https://technet.microsoft.com/en-us/library/ff678296.aspx

There's nothing I see in your code or log that indicates a problem and the file imports fine when I specify my path - albeit empty. Can you import other files in that location?

[ChrisHemedinger]

I suspect that EG is local, SAS is remote, and the file is on a locally mapped H drive.  The SAS session can't see that H drive.  If @Ahsan shares the code that the Import Data task generates, we might be able to confirm.

The Import Data task handles all of this by copying the local file to the remote SAS, and running the INFILE code from there.  If you want to adapt to your own independent program, then you have to follow the steps I've shared.

[Reeza]

That was my first thought, but when you get that error on the server, it usually has the server path and the H: part appended...unless the server also has an H drive I suppose?

[Ahsan]

how do we find a server path