topic How to add header text in first two columns of row 1 in a csv proc export in SAS Procedures

How to add header text in first two columns of row 1 in a csv proc export

mmm7 — Wed, 30 Sep 2020 19:24:17 GMT

Background: I'm preparing multiple data submissions and the upload requirements include two things: 1) data must be in a csv format and 2) the first row must include the form name and version number. Essentially, when viewing data from a spreadsheet, form name must be in cell A1 and version number needs to be in cell B1. The variable names are located in row 2.

I have no difficulty with exporting the data as a csv. I'm seeking help with programming the form name and version number into the first row and first two columns of the csv. I've been adding the information manually but it's getting tiresome!

Re: How to add header text in first two columns of row 1 in a csv proc export

Kurt_Bremser — Wed, 30 Sep 2020 20:02:01 GMT

PROC EXPORT creates a data step and runs it. That data step can be found and copy/pasted from the log.

In this data step there is a statement run at _N_ = 1 that writes the header. You can add your additional header line there.

Re: How to add header text in first two columns of row 1 in a csv proc export

mmm7 — Thu, 01 Oct 2020 12:08:50 GMT

Hi Kurt,

Thanks so much for the guidance! Below is the proc export code from the log. I tried to manipulate the _n_ = 1 statement to meet my needs to no avail. Would you be willing to walk me through this further? I appreciate your time and help.

data _null_;
      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
      %let _EFIREC_ = 0;     /* clear export record count macro variable */
      file 'S:\TEXT.csv' delimiter=',' DSD DROPOVER lrecl=32767;
      if _n_ = 1 then        /* write column names or labels */
       do;
         put
            "VAR1"
         ','
            "VAR2"
         ','
            "VAR3"
         ','
            "VAR4"
         ;
       end;
     set  IMPORT   end=EFIEOD;
         format VAR1 best12. ;
         format VAR2 $1. ;
         format VAR3 best12. ;
         format VAR4 $2. ;
       do;
         EFIOUT + 1;
         put VAR1 @;
         put VAR2 $ @;
         put VAR3 @;
         put VAR4 $ ;
        ;
       end;
      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
      if EFIEOD then call symputx('_EFIREC_',EFIOUT);
run;

@Kurt_Bremser

Re: How to add header text in first two columns of row 1 in a csv proc export

Kurt_Bremser — Thu, 01 Oct 2020 12:22:35 GMT

Add a PUT statement:

data _null_;
      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
      %let _EFIREC_ = 0;     /* clear export record count macro variable */
      file 'S:\TEXT.csv' delimiter=',' DSD DROPOVER lrecl=32767;
      if _n_ = 1 then        /* write column names or labels */
       do;
         put "Form: xxx,Version: yyy";
         put
            "VAR1"
         ','
            "VAR2"
         ','
            "VAR3"
         ','
            "VAR4"
         ;
       end;

Re: How to add header text in first two columns of row 1 in a csv proc export

Tom — Thu, 01 Oct 2020 12:42:19 GMT

There is no need to use PROC EXPORT to write a CSV file. A data step will do the same easily.

Let's assume you have two datasets one named FORM with variables NAME and VERSION and one named HAVE with the data. Let's assume you have a fileref CSV that points to the file you want to create.

First get a list of the names of the variables in the HAVE dataset (so you can write the header row).

proc transpose data=have(obs=0) out=names;
  var _all_;
run;

Now write the two header rows and the data to the same file. The MOD option on the last two FILE statements means the new lines are appended to the end of the existing file.

data _null_;
  file csv dsd ;
  set form;
  put name version;
run;
data _null_;
  file csv dsd mod;
  set names;
  put _name_ @@;
run;
data _null_;
  file csv dsd mod;
  set have;
  put (_all_) (+0);
run;

Re: How to add header text in first two columns of row 1 in a csv proc export

mmm7 — Thu, 08 Oct 2020 12:24:58 GMT

Thanks for your example! It worked perfectly! @Kurt_Bremser