topic Re: Length of first row in csv greater than 32767 - Proc Import not reading in variable names correc in SAS Procedures

Length of first row in csv greater than 32767 - Proc Import not reading in variable names correctly

neeld1 — Thu, 06 Feb 2020 16:38:02 GMT

Hello All,

I am a noob sas user. I'm more proficient in R, but SAS is totally new to me. I'm facing multiple issues reading a large file too.

proc import
	datafile = ".\my_data.csv"
	out = work.imported_data
	dbms = csv
	replace;
	guessingrows = 1000;
run;

The csv file that I'm trying to import does not read in the correct variable names. My csv has 3055 variables.

Currently, proc export is reading the variables names up to 2933 variables correctly and then renaming the remaining variables as VAR2934, VAR2935, VAR2936... VAR3055.

I have tried getnames = no and obs = 1 option as well. The first row does not read in correctly. I've found the issue on google. It seems that the LRECL of the first row is greater than 32767 bytes/characters.

Do you know how I can change the LRECL to read in the data correctly?

I have attached the data and code for your reference.

Currently I have to manually rename the variables.

Please suggest. Your help is much appreciated.

Regards,
Neel

Re: Can a SPSS SAV file be compressed in PROC EXPORT?

neeld1 — Thu, 06 Feb 2020 16:25:17 GMT

@SASKiwi - Thank you so much for your response.

I am a noob sas user. I'm more proficient in R, but SAS is totally new to me. I'm facing multiple issues reading a large file too.

I can create a new thread or post down below:

The csv file that I'm trying to import does not read in the correct variable names. My csv has 3055 variables.

Currently, proc export is reading the variables names up to 2933 variables correctly and then renaming the remaining variables as VAR2934, VAR2935, VAR2936... VAR3055.

Do you know how I can change the LRECL to read in the data correctly?

proc import
	datafile = ".\&wave6dsn..csv"
	out = all&w6suffix.
	dbms = csv
	replace;
	guessingrows = 1000;
run;

Thanks,
Neel

Re: Length of first row in csv greater than 32767 - Proc Import not reading in variable names correc

Tom — Thu, 06 Feb 2020 16:44:56 GMT

I think this has been answered before. The solution is to create a fileref so you can tell SAS what record length to use.

filename csv  ".\my_data.csv" lrecl=2000000 ;

proc import
	datafile = csv
	dbms = csv
	out = work.imported_data
	replace
;
	guessingrows = 1000;
run;

Re: Length of first row in csv greater than 32767 - Proc Import not reading in variable names correc

neeld1 — Thu, 06 Feb 2020 17:07:51 GMT

Hello @Tom ,

Thank you for the response. Unfortunately, I've tried this solution before and it does not work.

You could download the data and syntax and try it as well. It does not seem to work.

Apologies, but I am a very new user to SAS.

Thanks again,

Neel

Re: Length of first row in csv greater than 32767 - Proc Import not reading in variable names correc

ballardw — Thu, 06 Feb 2020 17:13:42 GMT

If the text of two or more column headings is exactly the same for the first 32 characters then SAS will name the first column with the first 32 characters and remaining variable(s) with VAR and the column number because it doesn't have any fancy instructions on trying to parse the column headers multiple times to try to resolve the like issue.

An example: replace the path placeholder with a path that works on your system:

data _null_;
   file "<path>\longcolumnname.csv" ;
   put 'columnheadinglongerthan32charactersnumber1,columnheadinglongerthan32charactersnumber2';
   put '123,456';
run;

proc import datafile= "<path>\longcolumnname.csv"
     out=work.import
     dbms=csv
     replace;
run;

Re: Length of first row in csv greater than 32767 - Proc Import not reading in variable names correc

ChrisHemedinger — Thu, 06 Feb 2020 17:21:23 GMT

If you have SAS Enterprise Guide, you can use the Import Data task to bootstrap and generate DATA step and INFILE, which gives you more control over how a file is read. I've attached the generated code for your sample file.

Re: Length of first row in csv greater than 32767 - Proc Import not reading in variable names correc

neeld1 — Thu, 06 Feb 2020 17:39:04 GMT

Hello @ballardw ;

Appreciate the response. However, while uploading the sample data, I've ensured that each variable name is 32 characters or less and that there are no duplicate records. So the truncation will not occur in the datastep or proc import.

Even I thought this would have been the issue, but unfortunately it has more to do with the LRECL than with the variable length in my case.

Appreciate the code snippet and response.

Regards,

Neel

Re: Length of first row in csv greater than 32767 - Proc Import not reading in variable names correc

Tom — Thu, 06 Feb 2020 20:49:03 GMT

Just tell PROC IMPORT to ignore the header row.

filename csv "&path/my_data.csv" lrecl=2000000;
proc import datafile=csv out=test1 replace dbms=csv;
 getnames=no;
 datarow=2;
run;

You can use it to rename the variables yourself. So get the names that PROC IMPORT generated.

proc transpose data=test1(obs=0) out=names ;
 var _all_;
run;

Then read in the name from the first row and use them to generate RENAME statement.

data new_names;
  infile csv obs=1 dsd ;
  input new_name :$32. @@;
run;

filename code temp;
data _null_;
  file code;
  set names end=eof;
  if not eof2 then set new_names end=eof2;
  if _n_=1 then put 'rename';
  put _name_ '=' new_name  ;
  if eof then put ';';
run;

Then use PROC DATASETS to run the rename.

proc datasets nolist lib=work;
  modify test1;
  %include code;
run;quit;

Re: Can a SPSS SAV file be compressed in PROC EXPORT?

SASKiwi — Thu, 06 Feb 2020 20:05:06 GMT

@neeld1 - Please post the SAS log of your program.

Re: Length of first row in csv greater than 32767 - Proc Import not reading in variable names correc

neeld1 — Thu, 06 Feb 2020 21:04:26 GMT

Hello @ChrisHemedinger ,

Appreciate the solution. This solution does work!

I would have liked a more succinct solution that would not require me to open sas enterprise, how this does do the trick.

Thanks again,

Neel

Re: Length of first row in csv greater than 32767 - Proc Import not reading in variable names correc

neeld1 — Thu, 06 Feb 2020 21:21:00 GMT

@Tom - Bravo!

This is what I need. Thank you so much!

Really appreciate the help.

Regards,

Neel