vivi11130704
vivi11130704

Reputation: 451

Why my SAS column input doesn't work?

The raw database looks like this:

I used column input:

input 
PRODUCT_ID $ 1-47
PRODUCT_NDC $ 48-56
NDC_PACKAGE_CODE $ 58-69
PACKAGE_DESCRIPTION $ 71-325
;

run;

Every column is imported correctly except for "PACKAGE_DESCRIPTION".

The output looks like this:

I think it's because the content of "PACKAGE_DESCRIPTION" exceeds SAS text limit (there are some very long descriptions in the database). How should I fix this?

Thanks in advance!

Upvotes: 1

Views: 114

Answers (2)

Dominic Comtois
Dominic Comtois

Reputation: 10431

First, as I mentioned in my comment, I'd try something like this if there's a chance the line feeds expected by SAS are not of the proper type (also adding @Tom's recommendations which are also good):

data want;
  infile 'myfile.txt' lrecl=500 truncover TERMSTR=LF;
  input 
    PRODUCT_ID $ 1-47
    PRODUCT_NDC $ 48-56
    NDC_PACKAGE_CODE $ 58-69
    PACKAGE_DESCRIPTION $ 71-325
    ;
run;

If LF doesn't give wanted results, then try with CRLF, and then (although this is uncommon), CR.

Other options you can add to the infile statement are:

  • ENCODING= usually one of lat1, utf8 or ansi. You can check this by first opening the file in an advanced text editor such as Notepad++, EmEditor or CoolEdit -- it will show (in the lower status bar usually) what encoding is used. At the same time, you can determine what kind of linefeeds are used, so that you can use the appropriate one with the TERMSTR= option.
  • RECFM=F to state that your input file has fixed-length variables (not sure how that would affect the reading, but might be worth a try).

PS: I'd be surprised if all of that were to fail, but if it does, there is a hack I used in the past that worked for me in the past where nothing else had worked. I can't recall the precise circumstances unfortunately, but the "hack" was to add to my input text file a dummy numeric column after the last character variable.

PPS: I don't think the length of the PACKAGE_DESCRIPTION column is in itself problematic.

Upvotes: 0

Tom
Tom

Reputation: 51621

Most likely because of invalid settings on the INFILE statemnt. Add LRECL= and TRUNCOVER options.

infile 'myfile.txt' lrecl=500 truncover ;
input 
  PRODUCT_ID $ 1-47
  PRODUCT_NDC $ 48-56
  NDC_PACKAGE_CODE $ 58-69
  PACKAGE_DESCRIPTION $ 71-325
;

Upvotes: 2

Related Questions