topic Re: Infile Statment Help in SAS Programming

Infile Statment Help

Ahsan — Fri, 05 Jan 2018 18:52:32 GMT

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

Re: Infile Statment Help

Reeza — Fri, 05 Jan 2018 18:57:10 GMT

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.

Re: Infile Statment Help

Tom — Fri, 05 Jan 2018 19:50:46 GMT

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.

Re: Infile Statment Help

Ahsan — Fri, 05 Jan 2018 20:43:06 GMT

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

Re: Infile Statment Help

Reeza — Fri, 05 Jan 2018 20:45:21 GMT

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

Re: Infile Statment Help

Ahsan — Fri, 05 Jan 2018 20:49:22 GMT

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

Re: Infile Statment Help

Ahsan — Fri, 05 Jan 2018 20:51:40 GMT

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

Re: Infile Statment Help

Reeza — Fri, 05 Jan 2018 20:53:12 GMT

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.

Re: Infile Statment Help

ChrisHemedinger — Fri, 05 Jan 2018 20:53:13 GMT

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).

Re: Infile Statment Help

Ahsan — Fri, 05 Jan 2018 20:55:24 GMT

I have attached the log.

Re: Infile Statment Help

Reeza — Fri, 05 Jan 2018 21:01:37 GMT

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?

Re: Infile Statment Help

ChrisHemedinger — Fri, 05 Jan 2018 21:05:53 GMT

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.

Re: Infile Statment Help

Ahsan — Fri, 05 Jan 2018 21:08:23 GMT

I try the copy path technique it didn't work I saved in the other path still not working that's strange.

Re: Infile Statment Help

Reeza — Fri, 05 Jan 2018 21:11:46 GMT

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?

Re: Infile Statment Help

Ahsan — Fri, 05 Jan 2018 22:41:40 GMT

how do we find a server path

Re: Infile Statment Help

Patrick — Sat, 06 Jan 2018 02:33:25 GMT

@Ahsan

The following code will write mapped drives to the log as seen by the SAS server. That's what's available to your SAS program.

filename diskinfo DRIVEMAP;
data _null_;
  infile diskinfo;
  input;
  put _infile_;
run;

http://support.sas.com/kb/24/818.html

Re: Infile Statment Help

Kurt_Bremser — Sat, 06 Jan 2018 08:33:41 GMT

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

Re: Infile Statment Help

Ahsan — Wed, 10 Jan 2018 18:39:52 GMT

the sas file in on server and we using import function and it produces this code. In this code infile statement is used if I like to input new file using this code and change the file name in the infile statement can I do this.

DATA WORK.OIA_GL_DET_LEDGER_DAPG67_EXCEL;

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 '/sas/saswork/SAS_work309F00009EC8_trvlapp1981/#LN00014'

LRECL=170

ENCODING="LATIN1"

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;

Re: Infile Statment Help

Tom — Wed, 10 Jan 2018 21:09:00 GMT

In general YES. If you have a working data step, even one as ugly as the thing that EG generated for you, you can point it at a file with a similar structure and use it to create a dataset.

But the problem is that your current code was most likely created o read a text file that Enterprise Guide generated from a Excel spreadsheet. So if you have another Excel SpreadSheet you would need to perform the same types of modifications that EG did to create a similar delimited text file that the SAS dataset expects to read. There is no way that I know to perform just the Excel to text file step of EG. Certainly you cannot do it the non-windows server where your SAS session is running.

Re: Infile Statment Help

Ahsan — Wed, 10 Jan 2018 21:14:47 GMT

Tom is a txt file file the data set name is excel but it's a txt file format.