DATA Step, Macro, Functions and more

Rename Column

Reply
Contributor
Posts: 23

Rename Column

Hello, i would like to rename a column in data set.

example : i want to rename a column which name is &B_AccountNumber for Account.

the problem is that the name in macro variable &B_AccountNumber composed of two words(with blank, for example : &B_AccountNumber=Account Number). if &B_AccountNumber is composed from one word, algorithm works perfectly.

data Balance_update;

set Balance (rename=(&B_AccountNumber=Account));

run;

What should i do?

i tried to write (rename=('&B_AccountNumber'n=Account)) but is not work!


Thank you

Alexey

Super User
Super User
Posts: 7,942

Re: Rename Column

Hi,

I would firstly question why you want to do this in this fashion, there are many possibilities for mistakes.  Perhaps provide an example and it could be done differently.  As for your issue with blanks, not sure where B_accountnumber is created, I would suggest at that point would be the best to process the text into a useable format

data _null_;
  call symput('b_accountnumber',tranwrd("my model"," ","_"));
run;

data Balance_update;
  set sashelp.cars (rename=(make=&B_AccountNumber.));
run;

Contributor
Posts: 23

Re: Rename Column

Hello, thank you for your answer.

I import excel files, and in every excel i have different variable name. my aim is convert every variable name to unique variable name that will be identical across different files.

the next problem that the names of variable is in Hebrew... Sometimes they consist from two words with blank, sometimes, one word, sometimes three words.

Super User
Super User
Posts: 7,942

Re: Rename Column

Why not just do:

proc import datafile='....xls' out=temp replace;

     getnames=no;

     range='A1:...';  /* not sure if datarows=1 works here */

run;

and let proc import assign col1-colx to each variable?

Super User
Posts: 7,758

Re: Rename Column

Use "&B_AccountNumber"n.

Note the double quotes. Single quotes prevent the handling of macro elements.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,758

Re: Rename Column

Further question: what causes the column in Balance to have a name that does not meet the SAS requirements for a column/variable name?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 23

Re: Rename Column

Posted in reply to KurtBremser

Hello, thank you for your answer.

I import excel files, and in every excel i have different variable name. my aim is convert every variable name to unique variable name that will be identical across different files.

the next problem that the names of variable is in Hebrew... Sometimes they consist from two words with blank.

Super User
Posts: 7,758

Re: Rename Column

If the variables are actually "named" in Hebrew, then you will have to consider if SAS is able to handle DBCS at that level (syntax objects instead of just data) before renaming.

I used the double quotes because in Excel these are not names, just data in the first row.

You may be better off by

- exporting excel content into csv

- write a data step manually to import, starting in row 2.

That way you discard the different names for identical columns

BTW, did you try the double quotes?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 43

Re: Rename Column

You don't mention where you get the contents for your macro variable, so I will assume that you have multiple sources for the excel files you want to import.

In source A, account number is the third column, in source B, account number is the 5th column, etc.  So you have predefined macro variables:

%LET A_AccountNumber = AccountNum;

%LET B_AccountNumber = Customer Account;

%LET C_AccountNumber = Cust. Nbr.;

BUT we know that when SAS imports the excel file, it will convert all spaces and punctuation to underscores, so your variable names become:

%LET A_AccountNumber = AccountNum;

%LET B_AccountNumber = Customer_Account;

%LET C_AccountNumber = Cust__Nbr_;

IF you are already predefining, then you can take care of this right away.

Ask a Question
Discussion stats
  • 8 replies
  • 523 views
  • 0 likes
  • 4 in conversation