labels while proc exporting

[maggi2410]

Hi,

I have a sas dataset with columns having labels. While using the proc export, I want the labels to be the first row in the excel. I have tried label option and putnames=no too. But nothing works for me. Any idea?

Mine is 64 bit SAS and DBMS = excel(not xls) is used.

Thanks!!

[art297]

Have you tried the label option without the putnames option?

Take a look at: http://support.sas.com/kb/41/735.html

[maggi2410]

yes..nothing worked for me.. but i got this solution finally:

via libname statement use dslabel dataset option. But it doesnt replace your excel. So you have to add an extra piece of code to do so.

Thanks

[art297]

The following worked for me:

  /*Modify SASHELP.CLASS to include labels for variables*/

   data newclass;

     set sashelp.class;

     label name='First Name'

           sex='Gender'

           Age='Age of Person'

           Height='Height of Person'

           Weight='Weight of Person';

   run;

   proc export data=newclass outfile='c:\classlabel.xls'

               label dbms=excel replace;

   run;

[maggi2410]

Great. I guess it depends a lot in your sas version and your platform. Mine is 9.1 on UNIX. and somehow didnt work.

[art297 · Accepted solution]

That option came out with 9.2

[Ksharp]

Just for a fun. You can rename its name to its label before export excel file.

data newclass;
     set sashelp.class;
     label name='First Name'
           sex='Gender'
           Age='Age of Person'
           Height='Height of Person'
           Weight='Weight of Person';
   run;
options validvarname=any;
data _null_;
 set sashelp.vcolumn(keep=libname memname name label where=(libname='WORK' and memname='NEWCLASS')) end=last;
 if _n_ eq 1 then call execute('proc datasets library=work nolist;modify newclass;rename ');
 call execute(cats(name,"='",label,"'n" ));
 if last then call execute(';quit;');
run;



   proc export data=newclass outfile='c:\classlabel.xls'
                dbms=excel replace;
   run;

Ksharp

[data_null__]

Ksharp wrote:

call execute(cats(name,"='",label,"'n" )); if last then call execute(';quit;'); run;    proc export data=newclass

Check out NLITERAL function.

[Ksharp]

Ha. That could make my code better and stronger .

[ezetina]

I found this SAS sample useful

http://support.sas.com/kb/35/574.html

[Ksharp]

Of course. If you can accept the non-legitimacy EXCEL file , using PROC REPORT which default use LABEL as header of column.

data newclass;
     set sashelp.class;
     label name='First Name'
           sex='Gender'
           Age='Age of Person'
           Height='Height of Person'
           Weight='Weight of Person';
   run;
ods listing close;
ods tagsets.excelxp file='c:\x.xls' ;
proc report data=newclass  nowd;
run;
ods tagsets.excelxp close;
ods listing;

Ksharp