Adding Headers to Dataset Exporting as CSV

[Corinthian94]

Hi there,

I am trying to create a csv file that has both the variable names of the dataset and another line as the header. It should look like below.

I have tried proc export, ods csvall, and a data _null_ statement but can't seem to get it to work. Examples of my code below. Is there a way to add in the ndar_subject line header while keeping the variable names as the headers below? If I could get the variable names as a header in the data _null_ step that would be ideal, but I have not been able to figure this out.

%let header1 = %str(ndar_subject,1);

%put &header1;
data _null_;
file "R:\tsanche\REALM Data\Data\SAS Created Data\Data Repository\July 2024 CSV Upload Files\REALM Subject and Pedigree July 2024.csv" dsd;
set repdata.pedig;

if _n_ = 1 then do;
put "&header1";
end;

put (_all_)(+0);
run;

proc export data=repdata.pedig
outfile='R:\tsanche\REALM Data\Data\SAS Created Data\Data Repository\July 2024 CSV Upload Files\REALM Subject and Pedigree July 2024.csv'
dbms=csv
replace;
run;

ods csvall file="R:\tsanche\REALM Data\Data\SAS Created Data\Data Repository\July 2024 CSV Upload Files\REALM Subject and Pedigree July 2024.csv";
proc report data=repdata.pedig;
run;
ods csvall close;

Thanks in advance!

[ballardw]

First, and a sometimes very important bit, that picture is NOT of a csv file but of apparently a spreadsheet program that has opened a CSV. Which can seriously change some values from a CSV if not careful.

The Proc Export code generates a data step (data _null_ ) that can be either recalled to the editor or copied from the log, pasted into the editor, cleaned up (removing line numbers basically) and then modified.

Example:

Proc export data=sashelp.class
   outfile='X:\example.csv'
   dbms=csv
   replace;
run;

which generates this data step:

    data _null_;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    %let _EFIREC_ = 0;     /* clear export record count macro variable */
    file 'X:\example.csv' delimiter=',' DSD DROPOVER lrecl=32767;
    if _n_ = 1 then        /* write column names or labels */
     do;
       put
          "Name"
       ','
          "Sex"
       ','
          "Age"
       ','
          "Height"
       ','
          "Weight"
       ;
     end;
   set  SASHELP.CLASS   end=EFIEOD;
       format Name $8. ;
       format Sex $1. ;
       format Age best12. ;
       format Height best12. ;
       format Weight best12. ;
     do;
       EFIOUT + 1;
       put Name $ @;
       put Sex $ @;
       put Age @;
       put Height @;
       put Weight ;
       ;
     end;
    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
    if EFIEOD then call symputx('_EFIREC_',EFIOUT);
    run;

To place an additional line of text (or more) place PUT statement(s) in the DO; END; block before the Put statement that is placing the variable names in the document.

    if _n_ = 1 then        /* write column names or labels */
     do;
       put "First line header goes here";
       put "Second line of header goes here";
      /* repeat as desired */
       put
          "Name"
       ','
          "Sex"
       ','
          "Age"
       ','
          "Height"
       ','
          "Weight"
       ;
     end;

[Corinthian94 · Accepted solution]

This worked, thank you! Do you have any suggestions for doing this for multiple datasets to make it more efficient? I'm trying to think of ways to use macros here but don't think that would work since each individual dataset has different headers and variable names.

Thanks!

[ballardw]

Proc export the file.

Read that exported file.

For the first observation read write your headers

proc export data=sashelp.class
     outfile='X:\example.csv'
     dbms=csv
     replace;
run;

data _null_;
   infile 'X:\example.csv';
   file 'X:\example2.csv' ;

   input;
   If _n_=1 then do;
       put 'Header line one';
       put 'header line two';
       put _infile_;
   end;
   else put _infile_;
run;

There should be a way to modify the input text file in the data _null_ step with the File option MOD but it has been so many years since I did lots of text file stuff that I can't get the headers and not lose part of the other data. MOD is more designed for appending data to existing files.

There is a big caution: LRECL. You may have to specify a large LRECL option on both the INFILE and FILE statements if you are going to have lots of variables, or long values, in your source data set.

[Corinthian94]

Thank you! I will try this out as well. Appreciate your help!

[Ksharp]

ods _all_ close;
ods csvall file="c:\temp\REALM Subject and Pedigree July 2024.csv";
title "First line header goes here";
title2 "Second line of header goes here";
proc report data=sashelp.heart nowd;
run;
ods csvall close;