topic Re: Importation crops last variable for no apparent reason in SAS Enterprise Guide

Importation crops last variable for no apparent reason

sleretrano — Wed, 13 Jul 2016 11:57:42 GMT

I'm on SAS version 5.1.

I'm trying to import a text file into SAS. Using SAS Guide everything works fine.

Using the code generated by this importation, almost everything is fine. The last variable, however, is cropped. It's supposed to be a three digit number, but after the imporation I get a single digit.

Here's a sample of the code I'm using (I greatly reduced the number of variables which I suppose is only chaff).

DATA WORK.Output;
    LENGTH
        Var1            8
	Var2            8 ;
    FORMAT
        Var1        BEST12.
	Var2        BEST3. ;
    INFORMAT
        Var1        BEST12.
	Var2        BEST3. ;
    INFILE "Path"
 	LRECL=324
    DLM=':'
    MISSOVER
    DSD ;

    INPUT
        Var1        : ?? BEST12.
	Var2        : ?? BEST3. ;
RUN;

Any clues about what's happening? How can I fix this?

Thanks.

Re: Importation crops last variable for no apparent reason

RW9 — Wed, 13 Jul 2016 12:12:43 GMT

Hi,

I imagine you mean you are using EG 5.1 (SAS is currently at 9.4). It is likely to be this line:

LRECL=324

Which limits the characters per line to 324, any reason why you put this in, most of the time I would leave it out or set to 32767.

Also some tips:

Why are you using a : as a delimiter? Comma would be the normal, but occasionally pipe could be used. Seems odd.

Why read in variables with ?? - this just seems like you don't know what data is going to come in?

No need to code all in uppercase - its quite hard to read, and use spaces to indent rather than tabs which render differently between applications.

So your code could be written as:

data work.output;
 length var1 var2 8;
 format var1 best12. var2 best3.;
 informat var1 best12. var2 best3.;
 infile "path" lrecl=32767 dlm=':' missover dsd;
 input var1 var2;
run;

Re: Importation crops last variable for no apparent reason

data_null__ — Wed, 13 Jul 2016 12:28:11 GMT

Your problem is likely related to TERMSTR

http://support.sas.com/kb/14/178.html

SAS is probably reading CR '0d'x as the last character in field 2, which is not valid in a number field. Your use of ?? to suppress the associated messages are preventing you from seeing what is happening.

Try adding TERMSTR=CRLF on the INFILE statement.

Re: Importation crops last variable for no apparent reason

sleretrano — Wed, 13 Jul 2016 12:30:02 GMT

Yes, I'm using SAS EG 5.1, I'm really not aware of the different SAS that exist, hence the misinformation. Thanks.

You got it right, it's exactly the LRECL parameter that was screwing things up. I had it at 324 because that's what the generated code had in it.

I use : as a delimiter because that's what people use around here (at work). I wasn't aware that commas are more conventional, but I really can't see how that matters.

The ?? are there because, again, the generated code had it too. As you can guess, I don't know what I'm doing. I'm learning by seeing examples.

Thanks for all the tips and the answer.

Re: Importation crops last variable for no apparent reason

sleretrano — Wed, 13 Jul 2016 12:43:06 GMT

Actually I did try that, but it didn't work.

The DLM SAS generated was '7F'x though, not CR '0d'x, if that matters.

Thanks.

Re: Importation crops last variable for no apparent reason

RW9 — Wed, 13 Jul 2016 14:10:22 GMT

Just to add, Comma Separated Variable files - whilst officially documented as a standard - is widely used around the world for data transfer. If you open a CSV in Excel it will automatically populate values into columns for instance. The : is used in different ways for different applications, and so could be confusing. Its fine if its just within your company, unless anyone new joins, but going outside...

https://en.wikipedia.org/wiki/Comma-separated_values