Sample 36106: Exporting SAS® variable names and labels to the same cell in the top row of a Microsoft Excel workbook
The sample on the Full Code tab in this note enables you to export a SAS variable name and a SAS label from a SAS data set to the same cell in the top row of an Excel workbook.
The only restriction is that you cannot have a period (.) in the SAS label name.
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 sample enables you to export SAS variable names and SAS labels from a SAS data set to the same cell in the top row of an Excel workbook.
**********************************************************************************;
* Varlabelmaker9 *;
* purpose: To export variable names and labels to the same cell in the top row *;
* of an Excel worksheet. *;
* Parameters required: Names of the SAS data set, the Excel workbook, and *;
* worksheet 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 the SAS data set */
'c:\sastest\book1.xls', /* Name of the Excel file; keep the quotation marks.*/
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 sample enables you to export a SAS variable name and a SAS Label to the same cell in the top row of an Excel workbook.
| Type: | Sample |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> MS Excel
|
| Date Modified: | 2009-10-21 13:02:20 |
| Date Created: | 2009-06-01 11:53:57 |
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft Windows NT Workstation | 9.1 TS1M3 | |
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | |
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Advanced Server | 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 | |
