Sample 35574: Exporting variable names and labels to the same top cell in Microsoft Excel
You can use the Varlablemaker9 program (see the Full Code tab) to write variable names and their labels to the same top row in an Excel worksheet. The program creates a new data set from the original SAS data set, and it creates new labels from a combination of the existing variable name and the label.
This program is macro driven, so it only requires changes in the macro invocation to run. You can also download the program from the following SAS FTP site. To download the program, click the Downloads tab in this note.
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
program is macro driven and all parameter changes are at the bottom of the program. No internal changes are required to allow the program to run.
**********************************************************************************;
* Varlabelmaker91 *;
* purpose: to export variable names and labels to the same top cell in an excel *;
* worksheet *;
* Parameters required , name of the sas dataset, excel workbook, worksheet, and *;
* a location to write the temporary labeler routine *;
**********************************************************************************;
options mprint macrogen symbolgen;
%macro labelvar(sasdata,xlsname,sheetnam,sasprog);
options source2;
ods trace on;
ods output variables=varvol1;
proc contents data=&sasdata;
run;
ods trace off;
run;
data varvol2; set varvol1;
mergednewvar='"'||left(trim(variable))||'-'||left(trim(label))||'"';
run;
data _null_; set varvol2;
file "&sasprog";
put 'label ' variable ' = ' mergednewvar ';' ;
run; quit;
data temp1; set &sasdata;
%include "&sasprog";
run;
libname myexcel excel &xlsname;
proc sql;
drop table myexcel.&sheetnam;
quit;
data myexcel.&sheetnam (dblabel=yes); set temp1;
run;
quit;
libname myexcel clear;
%mend;
%labelvar(sasuser.baseball, /* name of sas dataset */
'c:\sastest\book1.xls', /* name of the excel file, keep the quotes*/
newsheet, /* name of the worksheet */
c:\sastest\mylabeler.sas); /* name of the place to write the sas label routine */;
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
This program exports both the variable name and the label to the same top header cell in Excel
| Date Modified: | 2009-10-21 12:58:50 |
| Date Created: | 2009-04-15 17:32:05 |
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M3 | |
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 | |
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | |
| Microsoft Windows NT Workstation | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 | |
| Microsoft Windows XP Professional | 9.1 TS1M3 | |
| Windows Vista | 9.1 TS1M3 | |
