topic Re: Renaming variables that don't have a space to have a space in SAS Procedures

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

SasGuy614 — Fri, 16 Jan 2015 10:12:35 GMT

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

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

RW9 — Fri, 16 Jan 2015 10:26:44 GMT

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.

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

Ksharp — Fri, 16 Jan 2015 10:32:16 GMT

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

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

SasGuy614 — Tue, 20 Jan 2015 21:49:29 GMT

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);

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

Reeza — Tue, 20 Jan 2015 22:09:55 GMT

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.

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

SasGuy614 — Wed, 21 Jan 2015 03:32:06 GMT

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;

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

Reeza — Wed, 21 Jan 2015 03:58:17 GMT

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

Base SAS(R) 9.2 Procedures Guide

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

Ksharp — Wed, 21 Jan 2015 04:59:12 GMT

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

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

SasGuy614 — Wed, 21 Jan 2015 16:26:44 GMT

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