SAS Export blank columns

[PetePatel]

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

[PeterClemmensen]

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.

[PetePatel]

I tested it for one row and checked.

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

[ballardw]

---

@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?

[Tom · Accepted solution]

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;

[PetePatel]

Thanks Tom!

[PetePatel]

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

[VDD]

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.