SAS Tip of the Month
Last month we looked at importing some data from Excel, this month we are going to look at the converse, exporting some data to Excel.
From the start, there are a number of ways of doing this but I am just looking at the basic, put the data out to a sheet with variable names in the first row. There are ways to format cells, color code cells or rows, and a number of other things, but that is beyond this tip.
First some data:
data student; length name $20 sex $1 age 8; input name $ sex $ age; cards; Alexandra F 15 Bailey F 16 Blake M 18 Caroline F 16 Dominic M 13 Jose M 17 Justin M 18 Melanie F 15 Oliver M 15 Sydney F 14 ; run;
Once we run this dataset you will see that their are three variables and ten observations.
How do we get this into Excel? Last month we looked at PROC IMPORT, well there is a related procedure called PROC EXPORT for getting the data out. The basic syntax for the use of the procedure is:
PROC EXPORT DATA=dataset_name FILE="filename" DBMS=EXCEL REPLACE; SHEET="sheet_name"; RUN;
The DBMS=EXCEL option tells SAS that the data being created will be in an Excel spreadsheet, while the REPLACE option is necessary if you want to overwrite an existing sheet (too many imports of data have failed because the programmer assumed any dataset of the same name would be automatically overwrittern when it is not!).
There are other statements that can be used including MIXED and RANGE, but these are beyond the example used here -- consult the SAS User Documentation for the use of these statements.
With this knowledge, lets export the data to the Excel file:
PROC EXPORT DATA=student FILE="C:\TEMP\Student.xls" DBMS=EXCEL REPLACE; SHEET="StudentDemographics"; RUN;
Once this is run, the Excel spreadsheet with the data from the dataset is in the file Student.xls in the tab StudentDemographics.
One thing to note using this method, the variable names are sent to the first row of the spreadsheet -- it is not possible to send variable label text using this method.
I hope this has been helpful. See you in October!
Updated September 2, 2013