topic Re: SAS Export blank columns in SAS Programming

SAS Export blank columns

PetePatel — Fri, 16 Nov 2018 16:05:50 GMT

Hi,

I am trying to export a large dataset ('test') from base SAS into a CSV file for modelling.

It contains 20k rows and 5,000 columns.

The code I am using is below:

proc export data=test outfile=
"C:\Users\pete\Desktop\test.csv"
dbms=csv replace;
run;

The first 1625 column names appear, however the rest are blank (although the values are fully populated).

Should I be adding an option to write all data to the CSV file?

Any help would be greatly appreciated.

Thanks

Re: SAS Export blank columns

PeterClemmensen — Fri, 16 Nov 2018 16:20:04 GMT

How do you verify that the CSV file has only 1625 columns? It sounds like the somewhat maximum amount of columns that an Excel spreadsheet can comprehend.

Re: SAS Export blank columns

PetePatel — Fri, 16 Nov 2018 16:26:32 GMT

I tested it for one row and checked.

Is there another option to export from SAS dataset to CSV?

Re: SAS Export blank columns

ballardw — Fri, 16 Nov 2018 16:38:14 GMT

@PetePatel wrote:

I tested it for one row and checked.

Is there another option to export from SAS dataset to CSV?

Please look closely at @PeterClemmensen's question.

Looking at CSV files with spreadsheets often hides much. For instance Excel, depending on version, has different numbers of columns it can display. If your data set has more variables than the spreadsheet software has available columns than it just plain cannot display them even if present.

So, HOW did you check? Did you open it an spreadsheet? then see above.

Did you count the number of header items in the CSV?

Re: SAS Export blank columns

Tom — Fri, 16 Nov 2018 16:44:02 GMT

That is a lot of variables. Sounds like the list of names was just too long for PROC EXPORT to write to the file.

Fortunately it is trivial to generate you own CSV file without using PROC EXPORT. Plus it will probably run a little faster.

First write the header row.

proc transpose data=test(obs=0) out=_names ;
  var _all_;
run;
data _null_;
  file "C:\Users\pete\Desktop\test.csv" dsd lrecl=1000000 ;
  set _names end=eof;
  put _name_ @ ;
  if eof then put;
run;

Then append the data:

data _null_;
  file "C:\Users\pete\Desktop\test.csv" dsd lrecl=1000000 MOD ;
  set test;
  put (_all_) (+0);
run;

Re: SAS Export blank columns

VDD — Fri, 16 Nov 2018 16:49:01 GMT

I have found that if I open CSV files in a text editor like notepad then I place the cursor on the last column I get a better visual than Excel provides.

Re: SAS Export blank columns

PetePatel — Fri, 16 Nov 2018 17:37:29 GMT

Thanks Tom!

Re: SAS Export blank columns

PetePatel — Sun, 18 Nov 2018 10:34:57 GMT

Hi Tom,

Do you know if there is a way to encode to UTF-8 within this code? This is the format I require for a program I am using to import the data into.

Thanks