topic Re: Column header truncated when exporting to csv / Export large column header in SAS Programming

Column header truncated when exporting to csv / Export large column header

Olscream — Tue, 09 Jun 2020 16:35:40 GMT

Hello everyone,

I would like to export to the CSV format a SAS dataset with a large number of columns (~2200) with a total length of around 42000 characters when all column titles concatened. The output final CSV file doesn't exceed 400MB because of a low number of rows.

But when I open my CSV file, I see that more than 300 column names are missing. How is that possible ? Would you know how to fix it please?

More details :

-I've used a proc export to export as CSV.

-When I use a proc export to output a XLSX file, I don't get this problem.

-It seems that the problem is the total length of the concatened column names since it works well for the other rows which are shorter (when all concatened).

-It is like I need to set a LRECL option to a higher limit, but it doesn't exist in a proc export.

Thank you very much in advance for your help.

Best regards

Re: Column header truncated when exporting to csv / Export large column header

Reeza — Tue, 09 Jun 2020 16:39:28 GMT

Show your code.
Have you tried a data step and explicitly setting the record length?
How are you checking the CSV, via a text editor or Excel? FYI - don't use Excel it can sometimes not parse records correctly causing issues.

Re: Column header truncated when exporting to csv / Export large column header

Kurt_Bremser — Tue, 09 Jun 2020 16:43:11 GMT

The code created by proc export tries to write the header as one string, which exceeds the maximum length of character values (32767).

Locate the header in the code, and split it to two separate put statements with a line hold (@) in the first.

Re: Column header truncated when exporting to csv / Export large column header

Reeza — Tue, 09 Jun 2020 16:48:05 GMT

*Create demo data;
data class;
	set sashelp.class;
	label age='Age, Years' weight = 'Weight(lbs)' height='Height, inches';
run;

proc sql noprint;
create table temp as
select name as _name_, label as _label_
from dictionary.columns
where libname="WORK" and upcase(memname)="CLASS";
select cats(quote(name),"n") into :varList separated by ' '
from dictionary.columns
where libname="WORK" and upcase(memname)="CLASS";
quit;

data _null_;
file "&sasforum.\datasets\TwoLinesHeader.csv" dsd lrecl = 40000;
set class;
if _n_ = 1 then do;
do until(eof);
    set temp end=eof;
    put _name_ @;
    end;
put;

put (&varList) (:);
run;

Modified version of this which prints names and labels. I removed the labels section.

https://gist.github.com/statgeek/ae153e40af0d35dce02d1c8102ff3b94

Re: Column header truncated when exporting to csv / Export large column header

Olscream — Wed, 10 Jun 2020 08:02:13 GMT

Could you show a snippet of code in order to do that please ?

Re: Column header truncated when exporting to csv / Export large column header

Olscream — Wed, 10 Jun 2020 08:09:55 GMT

Thanks for your answer. However it could work but I got several errors when trying it, the first one being :

proc sql noprint;
select cats(quote(name),"n")into : varList separated by ' '
from dictionary.columns
where LIBNAME = upcase("WORK")
and MEMNAME = upcase("My_Table");
quit;
%put &varList.;

ERROR: The length of the value of the macro variable VARLIST (65540) exceeds the maximum length (65534). The value has been
truncated to 65534 characters.

But on the other hand, it works when removing the CATS and QUOTE functions such as :

proc sql noprint;
select name into : varList separated by ' '
from dictionary.columns
where LIBNAME = upcase("WORK")
and MEMNAME = upcase("My_Table");
quit;
%put &varList.;

%put &varList.;
Col1 Col2 Col3 ...

Would you know how to "overpass" this limit please ?

Re: Column header truncated when exporting to csv / Export large column header

Olscream — Wed, 10 Jun 2020 08:13:33 GMT

I do a simple export as following :

proc export data=My_Table
dbms=csv
outfile="&month_My_Table.csv"
replace;
run;

&month is a number like 201501.

I've noticed the error thanks Microsoft Excel 2016 but also in another programming language.

Re: Column header truncated when exporting to csv / Export large column header

Kurt_Bremser — Wed, 10 Jun 2020 08:40:08 GMT

If you work with

options validvarname=v7;

set, you don't need the CATS() and QUOTE() functions, because you can't have blanks in variable names.