How to export csv with a custom header and trailer rows

[waliaa]

I'm trying to create an output like below: 

HEADER 02032023,,,,,,,,,,,

x,y,d,d,....(data)

TRAILER 00000100,,,,,,,,,, 

Description - this is a csv comma delimited file (to be exported to server) with two custom rows added at the export step. HEADER and TRAILER are two literals and 02032023 is a date stored in a macro(ext_dt) and 000000100 should be the count of rows in a csv file. 

I'm a fairly new user of sas so could someone please assist in writing the best logic for creating the above output. 

Note: I don't have the row count stored in any variable at this stage. And I don't need to display the actual header row in the final csv file (that has column headings etc)

I've tried the below code but it's not creating the required output unfortunately 

data _null_;
file "&server./ouput2.csv" ;
set class nobs=nobs end=last;
if _n_ = 1 then do;
put Header &ext_dt;
end;
put;
if last then do;
put TRAILER &nobs;
end;
run;

Thanks v much

[ballardw · Accepted solution]

It really helps if you show the result you are getting and actually describe the bit that is not the required output.

One thing, I see nothing that writes the commas, or other character if you expect such, to delimit the values i.e. the CSV portion of the file name.

It is not clear if you expect to have column headers for each of the variables or what they might be.

Put expects something to put to the output. You have to list the variables as a minimum to get that output. Literal text, not represented by a variable is placed inside quotation marks. MACRO text would be treated as literal text but requires double quotes for the text to resolve.

Here is an example with a data set you should have that will create similar output to what I believe you requested:

%let ext_dt=02032023;

data _null_;
  file "&outpath.class.csv" dlm=',' dsd;
  set sashelp.class end=last;
   if _n_ = 1 then        /* write column names or labels */
      do;
       put 'HEADER ' "&ext_dt";
       /* if you want column headers for the variables using the varaible name*/
       put
          "Name,Sex,Age,Height,Weight"
       ;
   end;
   /* put the actual variable values*/
   /* it is a good idea to specify the format you want*/
    format Name $8. ;
    format Sex $1. ;
    format Age best12. ;
    format Height best12. ;
    format Weight best12. ;
   put Name Sex Age Height Weight;
   if last then put "TRAILER " _n_ z8.;
run;

I put code to create a version of your date so I could incorporate it.

Pieces that you were missing before:

On the File statement the Dlm=',' is what places the commas between values, DSD is important if any of your values may have commas embedded or if any variables are missing so that things line up. If the total length of the text you generate is long you may need to add LRECL=1800; or similar to provide enough character spaces in the output.

The first PUT for the _n_=1 is your header. I did not include the trailing commas as I don't know what your rules for that would be just place them after the macro variable inside the quotes if actually needed. The second Put is column headings for the values if needed. If you don't need that then remove it. Note that the commas are part of the literal text for a simple row that matches the order of the variables in the PUT statement later.

Formats are a good idea to control appearance if there is not already a defined format. Likely critical for date, time and datetime values but percentages and currency as well.

Then the PUT with the variable names to write the values to the file.

The "If Last" puts the literal text for your trailer and uses the _n_ automatic variable as the counter. The format Z8. following the _n_ says to print using 8 positions and pad with leading zeroes to fill the positions. You didn't explicitly state the number of positions. Again I didn't include trailing commas. If you need them add ',,,,,,' for the number of commas you need.  The _n_ counts iterations of the data step so in simple cases it counts rows and you can use it as desired not just to determine the first observation.

[waliaa]

Thanks very much @ballardw. Your solution worked for me with some tweaks based on the business requirements. The only thing that I'm stuck at now is how to use local Lan path for the export in the DATA step (after file). It throws an error saying insufficinet access to &LAN_Output\filename_&suffix..csv" although I can access that folder otherwise. However, the csv was exported to server successfully ("&server./dev_data/logs/filename.csv"). Do I have to move the file from server to lan in another step? Thanks

[ballardw]

---

@waliaa wrote:
Thanks very much @ballardw. Your solution worked for me with some tweaks based on the business requirements. The only thing that I'm stuck at now is how to use local Lan path for the export in the DATA step (after file). It throws an error saying insufficinet access to &LAN_Output\filename_&suffix..csv" although I can access that folder otherwise. However, the csv was exported to server successfully ("&server./dev_data/logs/filename.csv"). Do I have to move the file from server to lan in another step? Thanks

---

Any time that you have a question about behavior of any particular program it is a good idea to include the LOG. Quite often the answer is there. Networks might have issues depending on your SAS configuration because while you as person may have permissions if your SAS is executing from a server that particular server may not have the permissions to use that other network location. It may be a matter of getting permissions added to the server or the path that the server sees needs to be different because the location is referenced differently from there than for you.

You may need to get your SAS admin person and/or IT together to set everything needed. I would suggest writing a very small script. such a data step with a single PUT "This is example text." ; with NO macro code at all to demonstrate to others and get that to work writing to the correct location. The bring that back to your program.

Sometimes the issues are as simple as paths in one operating system are case sensitive and one machine really wants to see: drive/folder/subfolder/myfile.csv and your program code resolved to something like: drive/Folder/myfolder/myfile.csv .  These usually result in some flavor of a "file not found" but your install may vary.

[andreas_lds]

The statement

put;

writes an empty line to the file.

You have to name the variables that you want written in the put statement. See have look at the post by @ballardw showing what to do.

[Patrick]

One quick way to get code very close to what you need is to use Proc Export like in below sample:

options obs=0;
proc export 
  data=sashelp.class 
  dbms=csv 
  file="c:\temp\testout.csv" 
  replace
  ;
run;
options obs=max;

Proc Export will create SAS data step code and write it to the SAS log. This code will be very close to what you need. You only need to add the trailer portion which you've already got in the code you shared.

Setting option obs=0 will generate the data step code without processing the source data so it will execute quickly independent on the data volumes in your source table.

[Tom]

So make sure to use the DSD option so SAS will write commas between the values.

Also make sure to actually write the values instead of just a blank line.

There is no variable named Header in SASHELP.CLASS dataset. Beside you said you wanted the text HEADER written on the first line.

%let ext_dt=20230412 ;
data _null_;
  file "&server./ouput2.csv" dsd ;
  set sashelp.class end=last;
  if _n_ = 1 then put "HEADER &ext_dt";
  put (_all_) (+0);
  if last then put 'TRAILER ' _n_ z8.;
run;

Results

HEADER 20230412
Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
Carol,F,14,62.8,102.5
Henry,M,14,63.5,102.5
James,M,12,57.3,83
Jane,F,12,59.8,84.5
Janet,F,15,62.5,112.5
Jeffrey,M,13,62.5,84
John,M,12,59,99.5
Joyce,F,11,51.3,50.5
Judy,F,14,64.3,90
Louise,F,12,56.3,77
Mary,F,15,66.5,112
Philip,M,16,72,150
Robert,M,12,64.8,128
Ronald,M,15,67,133
Thomas,M,11,57.5,85
William,M,15,66.5,112
TRAILER 00000019

PS You mentioned that the extra digits on the HEADER line was supposed to represent a date so I used a digit string that has the date in year month day order so that it is unambiguous.  The example string you showed, 02032023, does not look like a date at all.  And we decide that for some reason the YEAR is the end when is that supposed to be a date in February or one in March?

[waliaa]

Thanks very much @Tom. Your solution worked perfectly as well...appreciate your help! The other thing that I'm stuck at now is how to use local Lan path for the export in the DATA step (after file). It throws an error saying insufficinet access to &LAN_Output\filename_&suffix..csv" although I can access that folder otherwise. However, the csv was exported to server successfully ("&server./dev_data/logs/filename.csv"). Do I have to move the file from server to lan in another step? Thanks