Renaming variables that don't have a space to have a space

[SasGuy614]

Hello - I have a request to change all of the variable names for a report that I created. I will be using proc export to Excel to distribute the report. Is there a way to change variable names to include spaces?

Example

rename NbrOfAccts = 'Number Of New Accts'n; - This didn't work

Old Variable       New Variable

NbrOfNewAccts = Number Of New Accts

[RW9]

Use ods tagsets.excelxp and proc report.  You have lots of functionality for layout, labels, styling etc.

Alternatively write a datastep which outputs the data as you want to CSV (which Excel can read).

I can only think of one reason - using a old style Office binary format is small - for using export, and even that nowadays is questionable.

[Ksharp]

You need to use   options validvarname=any ;

But for your purpose I would like add a label for the variable , and use LABEL option in proc export  to direct its label into excel.

Xia Keshan

[SasGuy614]

I tried this a few times and I'm still not having success.The variable  'NbrOfNewHhlds' is the same after I run and export via proc export to Excel.

If I interpreted your recommendation correctly...below is what I did:

options validvarname=any;

* Export to Excel - Exporting each chart to a separate tab */

%macro P1_C1 (dmob,dname);

PROC EXPORT DATA= FINAL.P1_C1_MOB_&DMOB._&DNAME._&ReportDate.

        outfile= "/opt/apps/sas/MKT/PCG/New_Hhld_Activity_YTD/Final/PRIVATE_BANKING_HHLD_REPORT_&ReportDate.TEST.xlsx"

        dbms=xlsx replace;

       label NbrOfNewHhlds = 'Number of New HHs'

        sheet="MOB_&DMOB.";

run;

%Mend P1_C1;

%P1_C1 (0,,HH);

[Reeza]

You need to apply the label to the data set and then use proc export.

You can use either a data step or proc datasets to change the label before the export.

[SasGuy614]

Am I missing something obvious. In the first part below I used your suggestion to rename but when I use the proc export, the column name is still in its original format. I don't use the label option often. If you have suggestions or links where I can read please feel free to provide so that I can get up-to-speed with the rest of the SAS community .

options validvarname=any;

%macro P1_C1 (dmob,dname);

data TEST;

    set FINAL.P1_C1_MOB_&DMOB._&DNAME._&ReportDate.;

    label NbrOfNewHhlds = 'Number of New HHs';

run;

%Mend P1_C1;

%P1_C1 (0,HH);

/* Export to Excel - Exporting each chart to a seperate tab */

PROC EXPORT DATA= TEST

        outfile= "/opt/apps/sas/MKT/PCG/New_Hhld_Activity_YTD/Final/PRIVATE_BANKING_HHLD_REPORT_&ReportDate.TEST.xlsx"

        dbms=xlsx replace;

        sheet="TEST_DATA";

run;

[Reeza]

You don't have the label option in proc export.

Base SAS(R) 9.2 Procedures Guide

[SasGuy614]

Thanks....this seems to work. My follow-up question is that I have the label option within proc rank. However the two variables that I am ranking I need to modify their variable names as well. I really don't want to created another data set for the label option. Is there a dynamic way to label them within proc rank?

When I export the column names for the two variables listed below, it produces the following below. Ideally I would like for them to be labeled what I highlighted in black.

a) Rank for Variable TtlDeposit_InvBal

b) Rank for Variable TtlDeposit_InvBal_LoanBal

proc rank descending data=P1_C1 out=LABEL_TEST;

    var TtlDeposit_InvBal TtlDeposit_InvBal_LoanBal;

    ranks Dep_Inv_Rank Dep_Inv_Loan_Rank;

    label MonthOpened = 'Month Opened';

    label NbrOfNewHhlds = '# of New HHs';

    label NewDDAHhlds = '# of New DDA HHs';

run;

Thanks

[Ksharp]

You need label it before proc export , and don't forget the label option.

%macro P1_C1 ;

data TEST;

    set sashelp.class;

    label name = 'Number of New HHs';

run;

%Mend P1_C1;

%P1_C1

/* Export to Excel - Exporting each chart to a seperate tab */

PROC EXPORT DATA= TEST

        outfile= "c:\temp\xyz.xls"

        dbms=excel replace label;

        sheet="TEST_DATA";

run;

Xia Keshan