topic Re: Enterprise Guide vs Base SAS infile problem in SAS Programming

Enterprise Guide vs Base SAS infile problem

Ody — Thu, 16 Jun 2016 18:08:22 GMT

Hello all,

I've used EG to import a test file, primarily so I didn't have to write out all the code. The file imported successfully and all the data looks good.

I copied the generated code to Base SAS to include it in a larger program. I replaced the file location generated from EG with the actual network directory. When I try running the code in Base SAS I get a successful import message in my log:

NOTE: The infile "\\co\sit\FP\EP-JB1\Ltion\port.txt" is:
Filename=\\co\sit\FP\EP-JB1\Ltion\port.txt,
RECFM=V,LRECL=131068,
File Size (bytes)=23747766,
Last Modified=16Jun2016:03:47:16,
Create Time=09Dec2013:13:22:35

NOTE: 32136 records were read from the infile
"\\co\sit\FP\EP-JB1\Ltion\port.txt".
The minimum record length was 1.
The maximum record length was 737.

NOTE: The data set WORK.CHK has 32136 observations and 63 variables.
NOTE: DATA statement used (Total process time):
real time 1.29 seconds
cpu time 0.51 seconds

NOTE: Remote submit to EMSVR complete.

However, when I open the table it's blank. When I go back to the EG log I notice the following note at the top

/* --------------------------------------------------------------------
Code generated by a SAS task

Generated on Thursday, June 16, 2016 at 12:04:48 PM
By task: Import Data Wizard

Source file: \co\sit\FP\EP-JB1\Ltion\port.txt
Server: Local File System

Output data: WORK.port
Server: SASApp

Note: In preparation for running the following code, the Import
Data wizard has used internal routines to transfer the source data
file from the local file system to SASApp. There is no SAS code
available to represent this action.
Some characters embedded within the text file were translated to
alternative characters so as to avoid transmission errors.
-------------------------------------------------------------------- */

It seems EG is some how manipulating the original text file prior to import. I suspect because some of the fields have an NPSC character values of "?".

Here is the EG generated code. Note, I removed the lengthy variable list for the sake of sanity.

DATA WORK.port;
LENGTH
var1
var2
var3
etc...
;

FORMAT
var1
var2
var3
etc... 
;

INFORMAT
var1
var2
var3
etc...
;

INFILE 'J:\SASWORK\PLI01040IIHU\#LN00056'
LRECL=4843278
ENCODING="WLATIN1"
TERMSTR=CRLF
DLM='7F'x
MISSOVER
DSD 
;

INPUT
var1
var2
var3
etc...
;

RUN;

Here is my copy over to Base SAS with the updated directory which does not seem to work:

DATA WORK.port;
LENGTH
var1
var2
var3
etc...
;

FORMAT
var1
var2
var3
etc... 
;

INFORMAT
var1
var2
var3
etc...
;

INFILE '\\co\sit\FP\EP-JB1\Ltion\port.txt'
LRECL=4843278
ENCODING="WLATIN1"
TERMSTR=CRLF
DLM='7F'x
MISSOVER
DSD 
;

INPUT
var1
var2
var3
etc...
;

RUN;

I suspect because of the special characters in the original text file it's not properly importing the data. I've reviewed using the compress function to change the "?" but every thing Ive done so far has had the same result. I'd appreciate any assistance with this. Many thanks.

Re: Enterprise Guide vs Base SAS infile problem

ChrisNZ — Fri, 17 Jun 2016 03:39:58 GMT

What do you mean it is blank?

The log shows 32136 observations and 63 variables.

Every variable in every observation has length=0?

Re: Enterprise Guide vs Base SAS infile problem

Ody — Fri, 17 Jun 2016 12:44:36 GMT

It's strange, I know. When I use Base SAS to run the infile the log shows that it was successfully imported but when I open the table every column is blank. The headers are correct but there is no visible data in any of the fields.

I could be wrong but my suspicion is that it is due to the special characters in a few of the columns which EG somehow fixes (based on the note it creates) before the import process.

Re: Enterprise Guide vs Base SAS infile problem

Tom — Fri, 17 Jun 2016 13:07:26 GMT

Can you look at both the original file and the file as transferred by EG?

If every observation is messed up then you just need to look at the first two lines to see what is happening. First try just using the LIST command. In most instances if the line contains non-ASCII characters then that will trigger SAS to display the hexcodes for the characters in the line.

data _null_;
   infile 'myfile'  obs=2;
   input;
   list;
run;

Re: Enterprise Guide vs Base SAS infile problem

Ody — Fri, 17 Jun 2016 15:00:10 GMT

The file was loaded perfectly using EG. It matches the original text file; no garbled text, etc...

The note at the top of the EG generated code mentions some kind of character translation:

Note: In preparation for running the following code, the Import
Data wizard has used internal routines to transfer the source data
file from the local file system to SASApp. There is no SAS code
available to represent this action.
Some characters embedded within the text file were translated to
alternative characters so as to avoid transmission errors.

The original text file is ANSI and I think I may have found a possible problem. The very last line of the file contains an onk character. I'm not really sure how to remove this. I cant find the character on any special character tables.

Thoughts?

Re: Enterprise Guide vs Base SAS infile problem

Ody — Fri, 17 Jun 2016 15:18:03 GMT

Instead of trying to specifically replace the onk I was able to use compress and keep just the write-able data. I also change the DLM to "|" from the original given by EG.

I still need to verify the data translated successfuly but at least I now have data to verify.

Thanks for the help.


data chk;
infile "myfile" 
lrecl = 32767
TERMSTR = CRLF
DLM = '|'
MISSOVER
DSD
;

input @;
_infile_ = compress(_infile_, ,"kw")
;


INPUT
var1
var2
var3
etc...
;
run;

Re: Enterprise Guide vs Base SAS infile problem

rogerjdeangelis — Fri, 17 Jun 2016 20:02:44 GMT

* create a file with a bunch of problem characters;

data specialchars;
/* pdd has control type chars '01'x-'09'x */;
retain odd '00010203040506070809'x;
retain bf4 'ABCDEFGHIJ' aft '0123456789';
file "d:/txt/specialchars.txt";
do i=1 to 10;
put
bf4 $char10. odd $char10. aft $char10.;
end;
stop;
;run;quit;

* use the list option to see what hex codes may be present;

data lookatthem;
infile "d:/txt/specialchars.txt";
input;
list;
stop;
;run;quit;

LOG has

The stragler 30 is the record length

NOTE: The infile "d:/txt/specialchars.txt" is:
Filename=d:\txt\specialchars.txt,
RECFM=V,LRECL=384,File Size (bytes)=320,
Last Modified=17Jun2016:15:48:23,
Create Time=17Jun2016:15:48:23

RULE: ----+----1----+----2----+----3----+--

1 CHAR ABCDEFGHIJ..........0123456789 30

ZONE 444444444400000000003333333333
NUMR 123456789A01234567890123456789

A better way to look at the file is with fslist.

But you need full SAS for this.

proc fslist file="d:/txt/specialchars.txt";
;run;quit;

* this will give you a full screen visual of all 10 records
with the mappings, but best of all you get a command line.

On the command line type 'nums on;hex on;'

You can do finds for hex codes ie find '02'x.

Unfortunately the past function in the SAS forum does not show the SAS font mappings

of the specual characters, only FSLIST can ti this.

ABCDEFGHIJ 0123456789

444444444400000000003333333333
123456789A01234567890123456789

ABCDEFGHIJ 0123456789

444444444400000000003333333333
123456789A01234567890123456789