topic Re: first row exceed 32767, lrecl not work in SAS Procedures

first row exceed 32767, lrecl not work

abcd123 — Wed, 05 Aug 2015 17:13:47 GMT

Hello,

I am trying to import a csv file; first row contains variable names, and there are 10000 or so variables. My code look like following:

filename foo 'C:\Users\Desktop\aaaa_2.txt' lrecl=100000;

PROC IMPORT OUT= WORK.ABC

DATAFILE= foo

DBMS=TAB REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

Although I specified lrecl in the code, the generated variable names are still messed (not showing correct name, but just "VAR3526", "VAR3527" etc) . Here are the log message I got:

EFI will truncate records > 32767; your record length was 100000

Number of names found is less than number of variables found.

It seems SAS still truncates after 32767 characters. Why lrecl not work?

Re: first row exceed 32767, lrecl not work

ballardw — Wed, 05 Aug 2015 17:44:30 GMT

There are likely several work arounds for the lrecl issue but PROC IMPORT, which is not a very "bright" program may not recognize all of them.

One:

options LRECL= 100K;

Number of names found is less than number of variables found.

Tells us that some of your columns do not have headings to use as a variable name.

This will cause variables that do not have column headings to get names like VARxxxx where xxxx is the variable column number.

Also if your column headings exceed 32 characters and duplicate each other this may happen.

Re: first row exceed 32767, lrecl not work

abcd123 — Wed, 05 Aug 2015 18:09:21 GMT

Thanks, I tried to set options LRECL= 100K, however I got following error message: Option value for SAS option LRECL must be between 1 and 32767.

In addition , I checked my variable headings and all variable name length are less than 32. So it seems that it is the 32767 restriction that cause the problem.

Re: first row exceed 32767, lrecl not work

Ksharp — Thu, 06 Aug 2015 13:22:15 GMT

Can you press F4 to copy these code generated by proc import.

And paste it in a new editor window and change that LRECL= by hand ?

And from your log. It seems that it is not error about LRECL= , it is about the sas code .

Are you sure you specify the right delimiter ?

Message was edited by: xia keshan

Re: first row exceed 32767, lrecl not work

RW9 — Thu, 06 Aug 2015 13:44:09 GMT

10,000 variables, seriously? I would advise you look at the export process as that is a very high amount, databases wouldn't generally be happy with that amount of variables, nor would any other application. I would suggest the data be normalised before export, or be split up into manageable chunks, i.e. split out into various datasets with a linked id.

Re: first row exceed 32767, lrecl not work

Tom — Thu, 06 Aug 2015 13:47:16 GMT

Write the code to read it yourself. If you have no documentation on the variables then read them as character and analyze them in SAS.

data want ;

infile 'C:\Users\Desktop\aaaa_2.txt' dsd lrecl=1000000 truncover firstobs=2;

length var00001-var10000 $20 ;

input var00001-var10000 ;

run;

You can read the names separately ;

data names ;

length varname realname $32 ;

infile 'C:\Users\Desktop\aaaa_2.txt' dsd lrecl=1000000 truncover obs=1;

input realname @@ ;

varname = 'VAR' || put(_n_,Z5.);

run;

Re: first row exceed 32767, lrecl not work

abcd123 — Fri, 07 Aug 2015 21:37:18 GMT

Wow, this is exactly what I want!!! Thank you so much!!!