topic Re: Exporting data to CSV: More than 4,000 columns in SAS News and Q&A

Exporting data to CSV: More than 4,000 columns

Analyst_ke — Tue, 09 Nov 2021 16:11:22 GMT

Hi

I’m trying to export such a big data with more than 4,000 columns.

By “PROC EXPORT”, Headers are truncated after exporting.

Is there other solution for this issue?

Re: Exporting data to CSV: More than 4,000 columns

Tom — Wed, 09 Jun 2021 15:27:19 GMT

There is no need to use PROC EXPORT to write a CSV file. (PROC IMPORT to read them is a problem also but does add a little value if used to get a quick look at a file of unknown structure.)

To write the data a simple data step will do.

data _null_;
  set HAVE ;
  file 'want.csv' dsd ;
  put (_all_) (+0);
run;

If you also need a header row then write that first and then append the data.

proc transpose data=HAVE(obs=0) out=names ; run;
data _null_;
  set names;
  file 'want.csv' dsd ;
  put _name_ @;
run;
data _null_;
  set HAVE ;
  file 'want.csv' dsd mod ;
  put (_all_) (+0);
run;

Re: Exporting data to CSV: More than 4,000 columns

ballardw — Wed, 09 Jun 2021 15:30:54 GMT

One is a touch concerned about why there are a 4,000 to begin with.

Proc export generates data step code to write to the text file created.

You should be able to copy that from the log and paste into the editor. Clean it up to remove line numbers and such from the log.

Then add in the bits that are missing.

For example this is what the log shows for exporting the SASHELP.CLASS data set.

9        data _null_;
10       %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
11       %let _EFIREC_ = 0;     /* clear export record count macro variable */
12       file 'C:\Users\Owner\class.csv' delimiter=',' DSD DROPOVER lrecl=32767;
13       if _n_ = 1 then        /* write column names or labels */
14        do;
15          put
16             "Name"
17          ','
18             "Sex"
19          ','
20             "Age"
21          ','
22             "Height"
23          ','
24             "Weight"
25          ;
26        end;
27      set  SASHELP.CLASS   end=EFIEOD;
28          format Name $8. ;
29          format Sex $1. ;
30          format Age best12. ;
31          format Height best12. ;
32          format Weight best12. ;
33        do;
34          EFIOUT + 1;
35          put Name $ @;
36          put Sex $ @;
37          put Age @;
38          put Height @;
39          put Weight ;
40          ;
41        end;
42       if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
43       if EFIEOD then call symputx('_EFIREC_',EFIOUT);
44       run;

NOTE: The file 'C:\Users\Owner\class.csv' is:
      Filename=C:\Users\Owner\class.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=09Jun2021:09:26:43,
      Create Time=09Jun2021:09:26:29

Insert additional stuff into the Put after the "If _n_ = 1" to get the additional "headers".

Yes that's a lot of stuff to add depending on where the headers stopped appearing from the Export code.