DATA Step, Macro, Functions and more

Text File Import Automation

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Text File Import Automation

Hi All

 

I want to import daily text files into a SAS table.  I can easily do this by "hand" using File/Import and then go thru the wizard process.  The code that is created indicates that an import specification file is created, stored and referenced in a files\grid library directory.  When I re-run the SAS generated code, it references this directory (files\grid) to complete the re-running of the import. 

 

If i close the instance of SAS, the import sprecifiction reference is lost when I re-open it. 

 

How can I code this to run whenever I open SAS?

 

Sample:

'Funding Type'n $CHAR8.

'Fallout Function ID'n BEST3.

Mandatory BEST7. ;

INFILE '/sas/grid/SAS_work881E00001D85_69901mpn/#LN00042'   <---- This holds the specification information.

LRECL=403

ENCODING="LATIN1"

TERMSTR=CRLF

DLM='7F'x

MISSOVER

DSD ;

INPUT

'Loan Number'n : ?? BEST10.

Status : $CHAR6.

Channel : $CHAR1.

 

 

Thank you

 

Kody_Devl


Accepted Solutions
Solution
‎06-29-2016 03:11 PM
Contributor
Posts: 25

Re: Text File Import Automation

Posted in reply to Kody_devl

Dear Kody,

 

The INFILE statement is pointing to a temporary file which SAS has created in the WORK area. In order to reuse the generated SAS code, you should save it to a program file and then substitute the actual file name for the temporary filename on the INFILE statement. Also, change DLM= to match the actual delimiter in your data. Bottom line: The only thing really useful from the code generator is the INPUT statement and associated attribute statements, if any.

 

You can automate further by issuing a DIR command to grab the filenames and then use those filenames in the INFILE statement. For example, if you put the data step inside a macro called ReadFile with the filename as a parameter, you can do something like this:

 

filename dir pipe 'dir /b \\server\share\path\mask';
run;

fileanme doit TEMP;
run;

data _null_;
infile dir;
file doit;
input;
length line $ 200;
line = '%ReadFile(FN=' || trim( _infile_ ) || ');';
put line $char200.;
run;

filename dir clear;
run;

%include doit;

filename doit clear;
run;

HTH - Jack

View solution in original post


All Replies
Super Contributor
Posts: 439

Re: Text File Import Automation

Posted in reply to Kody_devl

What you see is what happens behind the scene. EG as a windows application initiates the import function by uploading your file to the server. From the pathname I conclude that this is a Linux / Unix machine. So there is a divide between your desktop (Windows) and where SAS actually runs (assuming a Linux host for the sake of argument). EG uploads the file to the work directory of the SAS session on the server (this SAS process is called the workspace server). It is a location that disappears when EG terminates the workspace server also exits and destroys that work location. Hence the code is not repeatable across EG sessions.

 

If you want to automate this you have the option of automating the upload and referring to that location as the INFILE. Another option is to stick the IMPORT task in a tab called AUTOEXEC and turn on the option to run that tab when the project is opened in EG.

 

Hope this helps,

- Jan.

Contributor
Posts: 58

Re: Text File Import Automation

Posted in reply to jklaverstijn

Jan

 

Thanks your for the behind the scenes explanation.  That helps. It is  a unix server and the text file has been copied to a Unix location (ie not my desktop)

 

I am not a expert in SAS and l like the idea that the wizard records the specification and stores it in the workspace location as you described AND creates the code / SQL to import the file.

 

You Suggest:

" option of automating the upload and referring to that location as the INFILE"

 

I would like to do this, using the wizard record the specification. How do I force it to store the upload specfication (not in a temporary workspace location) in a location that can be referenced in future "Uploads".

I am not sure how to do this.

 

Thanks

 

Solution
‎06-29-2016 03:11 PM
Contributor
Posts: 25

Re: Text File Import Automation

Posted in reply to Kody_devl

Dear Kody,

 

The INFILE statement is pointing to a temporary file which SAS has created in the WORK area. In order to reuse the generated SAS code, you should save it to a program file and then substitute the actual file name for the temporary filename on the INFILE statement. Also, change DLM= to match the actual delimiter in your data. Bottom line: The only thing really useful from the code generator is the INPUT statement and associated attribute statements, if any.

 

You can automate further by issuing a DIR command to grab the filenames and then use those filenames in the INFILE statement. For example, if you put the data step inside a macro called ReadFile with the filename as a parameter, you can do something like this:

 

filename dir pipe 'dir /b \\server\share\path\mask';
run;

fileanme doit TEMP;
run;

data _null_;
infile dir;
file doit;
input;
length line $ 200;
line = '%ReadFile(FN=' || trim( _infile_ ) || ');';
put line $char200.;
run;

filename dir clear;
run;

%include doit;

filename doit clear;
run;

HTH - Jack

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 272 views
  • 0 likes
  • 3 in conversation