BookmarkSubscribeRSS Feed
SasGuy614
Fluorite | Level 6

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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 Smiley Happy.

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;

SasGuy614
Fluorite | Level 6

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
Super User

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;

x.png

Xia Keshan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2546 views
  • 3 likes
  • 4 in conversation