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:datacars' 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:datacars.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:datacars.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 writecars'
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 writecars'
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;