Use PROC EXPORT to Write SAS Data to External Files and Excel Workbooks - Exchanging Data Between SAS and Microsoft Excel - page 25

27 format Returns dollar12. ;
28 do;
29 EFIOUT + 1;
30 put Region $ @;
31 put Product $ @;
32 put Subsidiary $ @;
33 put Stores @;
34 put Sales @;
35 put Inventory @;
36 put Returns ;
37 ;
38 end;
39 if _ERROR_ then call symputx('_EFIERR_',1);
40 if EFIEOD then call symputx('_EFIREC_',EFIOUT);
41 run;
The next listing is the first few lines of the output-delimited file. There are no headers listed in the first row
because the “PUTNAMES=NO” command suppressed the header row. Notice also that fields with commas
embedded in the data are enclosed in quotation marks.
Figure 2.7: Output in a CSV Format with PUTNAMES=NO Set Active.
Africa,Boot,Addis Ababa,12,"$29,761","$191,821",$769
Africa,Men's Casual,Addis Ababa,4,"$67,242","$118,036","$2,284"
Africa,Men's Dress,Addis Ababa,7,"$76,793","$136,273","$2,433"
Africa,Sandal,Addis Ababa,10,"$62,819","$204,284","$1,861"
Africa,Slipper,Addis Ababa,14,"$68,641","$279,795","$1,771"
Africa,Sport Shoe,Addis Ababa,4,"$1,690","$16,634",$79
Africa,Women's Casual,Addis Ababa,2,"$51,541","$98,641",$940
Africa,Women's Dress,Addis Ababa,12,"$108,942","$311,017","$3,233"
Africa,Boot,Algiers,21,"$21,297","$73,737",$710
Africa,Men's Casual,Algiers,4,"$63,206","$100,982","$2,221"
Africa,Men's Dress,Algiers,13,"$123,743","$428,575","$3,621"
Africa,Sandal,Algiers,25,"$29,198","$84,447","$1,530"
Africa,Slipper,Algiers,17,"$64,891","$248,198","$1,823"
Africa,Sport Shoe,Algiers,9,"$2,617","$9,372",$168
Africa,Women's Dress,Algiers,12,"$90,648","$266,805","$2,690"
Africa,Boot,Cairo,20,"$4,846","$18,965",$229
Example 2.3 PROC EXPORT Using the DBMS=EXCEL Option
This example runs PROC EXPORT three times. We do not need to define the SASHELP library, since it is
defined and initiated automatically by SAS at the beginning of each session. Each execution of a code
module places one output sheet into the Excel file. The first two times, we create sheets in the same
workbook showing examples of the options SHEET= and DBDSOPTS=. The “SHEET=” option defines
the Excel worksheet name. The second code module shows how to assign a mixed-case sheet name. Option
“DBDSOPTS=” allows several different options, but only the “KEEP=” option is shown.
The third time that PROC EXPORT is run, the output file is deleted and only one sheet is left in the file.
This is done with the “NEWFILE=YES” option. Quotes are not required around the sheet name as long as
it is a valid SAS name, containing neither spaces nor special characters. Unquoted sheet names will be all
capital letters when the code finishes running. This configuration did require SAS/ACCESS Interface to PC
Files, but did not require the PC Files Server. The data in all three of these worksheets is the same.
The three parts of this example all write Excel workbooks that do not need the PC Files Server to be
processed. The main point of these code routines is to show how to create multiple worksheets within one
workbook, and to create a sheet with a mixed-case name. Also note that only some of the input SAS dataset
variables are output.