SAS FAQ 
How do I write out a file that uses commas, tabs or spaces as delimiters to separate variables in SAS?

Note: This page is done using SAS version 9.2

Using proc export in SAS, you can easily write out your data to a file with values delimited by commas, tabs, spaces, or other characters. We will go through examples of how to write these out.  We will work with the outfile, dbms, and, when needed, the delimiter option and write out the cars dataset that can be found in the sashelp library. 

data cars; set sashelp.cars; run;

proc contents data = cars; run;

          Alphabetic List of Variables and Attributes

 #    Variable       Type    Len    Format      Label

 9    Cylinders      Num       8
 5    DriveTrain     Char      5
 8    EngineSize     Num       8                Engine Size (L)
10    Horsepower     Num       8
 7    Invoice        Num       8    DOLLAR8.
15    Length         Num       8                Length (IN)
11    MPG_City       Num       8                MPG (City)
12    MPG_Highway    Num       8                MPG (Highway)
 6    MSRP           Num       8    DOLLAR8.
 1    Make           Char     13
 2    Model          Char     40
 4    Origin         Char      6
 3    Type           Char      8
13    Weight         Num       8                Weight (LBS)
14    Wheelbase      Num       8                Wheelbase (IN)

If we wish to write out the dataset using a space as the delimiter, then we can give an outfile with or without an extension, specify dlm in the dbms option, and then provide the string to be used to separate values--in this case, ' '. Below, we have the SAS code followed by the first 5 lines of data in our output file.

proc export data=sashelp.cars
   outfile='D:\data\cars'
   dbms=dlm;
   delimiter=' ';
run;

Make Model Type Origin DriveTrain MSRP Invoice EngineSize Cylinders Horsepower MPG_City MPG_Highway Weight Wheelbase Length
Acura MDX SUV Asia All $36,945 $33,337 3.5 6 265 17 23 4451 106 189
Acura "RSX Type S 2dr" Sedan Asia Front $23,820 $21,761 2 4 200 24 31 2778 101 172
Acura "TSX 4dr" Sedan Asia Front $26,990 $24,647 2.4 4 200 22 29 3230 105 183
Acura "TL 4dr" Sedan Asia Front $33,195 $30,299 3.2 6 270 20 28 3575 108 186
Acura "3.5 RL 4dr" Sedan Asia Front $43,755 $39,014 3.5 6 225 18 24 3880 115 197

If we wish to write out our dataset as a comma-separated file, then we can modify our outfile, specify csv in the dbms option, and omit the delimiter line.

proc export data=sashelp.cars
   outfile='D:\data\cars.csv'
   dbms=csv;
run;

Make,Model,Type,Origin,DriveTrain,MSRP,Invoice,EngineSize,Cylinders,Horsepower,MPG_City,MPG_Highway,Weight,Wheelbase,Length
Acura,MDX,SUV,Asia,All,"$36,945","$33,337",3.5,6,265,17,23,4451,106,189
Acura,RSX Type S 2dr,Sedan,Asia,Front,"$23,820","$21,761",2,4,200,24,31,2778,101,172
Acura,TSX 4dr,Sedan,Asia,Front,"$26,990","$24,647",2.4,4,200,22,29,3230,105,183
Acura,TL 4dr,Sedan,Asia,Front,"$33,195","$30,299",3.2,6,270,20,28,3575,108,186
Acura,3.5 RL 4dr,Sedan,Asia,Front,"$43,755","$39,014",3.5,6,225,18,24,3880,115,197

If we wish to write out our dataset as a tab-separated file, then we can modify our outfile, specify tab in the dbms option, and omit the delimiter line.

proc export data=sashelp.cars
   outfile='D:\data\cars.txt'
   dbms=tab;
run;


Make	Model	Type	Origin	DriveTrain	MSRP	Invoice	EngineSize	Cylinders	Horsepower	MPG_City	MPG_Highway	Weight	Wheelbase	Length
Acura	MDX	SUV	Asia	All	$36,945	$33,337	3.5	6	265	17	23	4451	106	189
Acura	RSX Type S 2dr	Sedan	Asia	Front	$23,820	$21,761	2	4	200	24	31	2778	101	172
Acura	TSX 4dr	Sedan	Asia	Front	$26,990	$24,647	2.4	4	200	22	29	3230	105	183
Acura	TL 4dr	Sedan	Asia	Front	$33,195	$30,299	3.2	6	270	20	28	3575	108	186
Acura	3.5 RL 4dr	Sedan	Asia	Front	$43,755	$39,014	3.5	6	225	18	24	3880	115	197

Using underlying data step to write out file

When proc export is run in SAS, an underlying data step is generated using put statements with the variable names and values and formatting the variables according to the existing data formats.  You can find this data step in the log after running a proc export.  Below is the data step generated from the space-delimited proc export shown above. This data step can be modified to fit other specifications of how you wish to read out your data. 

367  proc export data=sashelp.cars
368     outfile='C:\SAS write\cars'
369     dbms=dlm;
370     delimiter=' ';
371  run;

372   /**********************************************************************
373   *   PRODUCT:   SAS
374   *   VERSION:   9.2
375   *   CREATOR:   External File Interface
376   *   DATE:      13NOV09
377   *   DESC:      Generated SAS Datastep Code
378   *   TEMPLATE SOURCE:  (None Specified.)
379   ***********************************************************************/
380      data _null_;
381      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
382      %let _EFIREC_ = 0;     /* clear export record count macro variable */
383      file 'C:\SAS write\cars'
383! delimiter=' ' DSD DROPOVER lrecl=32767;
384      if _n_ = 1 then        /* write column names or labels */
385       do;
386         put
387            "Make"
388         ' '
389            "Model"
390         ' '
391            "Type"
392         ' '
393            "Origin"
394         ' '
395            "DriveTrain"
396         ' '
397            "MSRP"
398         ' '
399            "Invoice"
400         ' '
401            "EngineSize"
402         ' '
403            "Cylinders"
404         ' '
405            "Horsepower"
406         ' '
407            "MPG_City"
408         ' '
409            "MPG_Highway"
410         ' '
411            "Weight"
412         ' '
413            "Wheelbase"
414         ' '
415            "Length"
416         ;
417       end;
418     set  SASHELP.CARS   end=EFIEOD;
419         format Make $13. ;
420         format Model $40. ;
421         format Type $8. ;
422         format Origin $6. ;
423         format DriveTrain $5. ;
424         format MSRP dollar8. ;
425         format Invoice dollar8. ;
426         format EngineSize best12. ;
427         format Cylinders best12. ;
428         format Horsepower best12. ;
429         format MPG_City best12. ;
430         format MPG_Highway best12. ;
431         format Weight best12. ;
432         format Wheelbase best12. ;
433         format Length best12. ;
434       do;
435         EFIOUT + 1;
436         put Make $ @;
437         put Model $ @;
438         put Type $ @;
439         put Origin $ @;
440         put DriveTrain $ @;
441         put MSRP @;
442         put Invoice @;
443         put EngineSize @;
444         put Cylinders @;
445         put Horsepower @;
446         put MPG_City @;
447         put MPG_Highway @;
448         put Weight @;
449         put Wheelbase @;
450         put Length ;
451         ;
452       end;
453      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
454      if EFIEOD then call symputx('_EFIREC_',EFIOUT);
455      run;

How to cite this page

Report an error on this page or leave a comment

The content of this web site should not be construed as an endorsement of any particular web site, book, or software product by the University of California.