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
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.
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
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);
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.
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;
You don't have the label option in proc export.
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
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.